﻿using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.PB;
using Coldairarrow.IBusiness;
using Coldairarrow.Util;
using Coldairarrow.Util.Helper;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_ProjectBusiness : BaseBusiness<PB_Project>, IPB_ProjectBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        public PB_ProjectBusiness(IDbAccessor db, IOperator @operator)
            : base(db)
        {
            _operator = @operator;
        }

        #region 外部接口

        public async Task<PageResult<PB_ProjectDTO>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_Project, Base_Department, Base_User, Base_Region, Base_Region, Base_Region, PB_ProjectDTO>> select = (a, b, c, d, e, f) => new PB_ProjectDTO
            {
                DepartmentName = b.Name,
                ContactsName = c.UserName,
                ProvinceName = d.Name,
                CityName = e.Name,
                AreaName = f.Name
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_User>() on a.ContactsId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_Region>() on a.ProvinceId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<Base_Region>() on a.CityId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    join f in Db.GetIQueryable<Base_Region>() on a.AreaId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f);
            if (!search.Keyword.IsNullOrEmpty())
            {
                var keyword = $"%{search.Keyword}%";
                q = q.Where(x =>
                      EF.Functions.Like(x.Code, keyword)
                      || EF.Functions.Like(x.Name, keyword));
            }

            //项目档案权限
            q = q.OrderByDescending(p => p.CreateTime);
            q = q.Where(x => _operator.Property.DeptIds.Contains(x.DeptId) && x.State == 0);
            q = q.Where(x => x.State == 0);
            return await q.GetPageResultAsync(input);
        }

        /*
         * 创建人：崔楠
         * 创建时间：2020-03-15 15:10
         * 描述：添加大屏项目接口
         */
        public async Task<PageResult<PB_ProjectDTO>> GetDataListNoTokenAsync(PageInput<ConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_Project, Base_Department, Base_User, Base_Region, Base_Region, Base_Region, PB_ProjectDTO>> select = (a, b, c, d, e, f) => new PB_ProjectDTO
            {
                DepartmentName = b.Name,
                ContactsName = c.UserName,
                ProvinceName = d.Name,
                CityName = e.Name,
                AreaName = f.Name
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_User>() on a.ContactsId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_Region>() on a.ProvinceId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<Base_Region>() on a.CityId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    join f in Db.GetIQueryable<Base_Region>() on a.AreaId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f);
            if (!search.Keyword.IsNullOrEmpty())
            {
                var keyword = $"%{search.Keyword}%";
                q = q.Where(x =>
                      EF.Functions.Like(x.Code, keyword)
                      || EF.Functions.Like(x.Name, keyword));
            }

            //项目档案权限
            q = q.OrderByDescending(p => p.CreateTime);
            q = q.Where(x => x.State == 0);
            //项目追踪串数据过滤
            q = q.Where(m => _operator.Property.DeptIds.Contains(m.DeptId));
            return await q.GetPageResultAsync(input);
        }

        /*
         * 创建人：崔楠
         * 创建时间：2020-03-30 14:46
         * 描述：添加大屏项目接口
         */
        public async Task<List<MaterialByLocaltion>> GetMaterialSumDataListAsync(string StorAreaName, List<string> RackName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select ");
            sql.Append("c.Location,");
            sql.Append("a.MaterialId,");
            sql.Append("b.`Name` MaterName,");
            sql.Append("b.Spec,");
            sql.Append("a.LocalId,");
            sql.Append("CAST((sum( a.Nums_N + a.Nums_O )) as SIGNED) Nums ");
            sql.Append("FROM ");
            sql.Append("IT_LocalMaterial a ");
            sql.Append("INNER JOIN PB_Material b ON a.MaterialId = b.Id ");
            sql.Append("left join View_LocationInfo c on a.LocalId = c.LocationId ");
            sql.Append("WHERE ");
            //sql.Append("a.LocalId IN (");
            string where = "";
            foreach (var id in RackName)
            {
                where += $"'{id}',";
            }

            where = where.Substring(0, where.Length - 1);
            //sql.Append("SELECT LocationId FROM View_LocationInfo WHERE StorAreaName = '"+ StorAreaName + "' AND RackName IN("+ where + ")");           
            //sql.Append(") ");
            sql.Append("c.StorAreaName = '" + StorAreaName + "' and ");
            sql.Append("c.RackName IN(" + where + ") ");
            sql.Append("GROUP BY ");
            sql.Append("a.MaterialId,");
            sql.Append("a.LocalId");
            return await Db.GetListBySqlAsync<MaterialByLocaltion>(sql.ToString());
        }

        /*
         * 创建人：崔楠
         * 创建时间：2020-0313 10:58
         * 描述：添加大屏项目详情接口
         */
        /// <summary>
        /// 大屏项目进度汇总
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<ProjectInfoDTO>> GetProjectInfoDataListAsync(PageInput<ConditionDTO> input)
        {
            List<ProjectInfoDTO> dto = new List<ProjectInfoDTO>();
            //根据项目热度获取项目
            ////var projs = await getProjectAsync();
            //if (projs.Count == 0)
            //{
             //var   projs = await getProjectLimitAsync();
            //}
            List<PB_Project> projs = new List<PB_Project>();

            var search = input.Search;
            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var q = GetIQueryable();
                var where = LinqHelper.True<PB_Project>();
                var newWhere = DynamicExpressionParser.ParseLambda<PB_Project, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
                projs = await q.Where(where).GetPageListAsync(input);
            }
            else
            {
                projs = await getProjectLimitAsync();
            }

            var InspectionInfo = await getInspectionAsync();
            var designs = await getDesignListByProjectAsync(projs);
            var SortingLists = await getSortingListByProjectAsync(projs);
            var notices = await getDeliveryNoticesByDesignIdAsync(designs);
            var returngoods = await getReturnGoodsByBuildingnumberIdAsync(designs);
            var pickupinfo = await getPickUpInfoByDeliveryNoticesItemIdAsync(notices);
            var transports = await getTransportBillByPickUpIdAsync(pickupinfo);
            var returnGoods = await getReturnGoodsByBuildingnumberIdAsync(designs);
            //项目回货重量(吨)
            var rerurncount = await GetReurnMaterialWeights(projs);
            foreach (var proj in projs)
            {
               
                //设计清单下辖物料重量汇总
                List<DesignMaterialCountDTO> designMaterialCounts = new List<DesignMaterialCountDTO>();
                //设计清单下装车物料重量汇总
                List<TransportBillCountDTO> transportBillCounts = new List<TransportBillCountDTO>();
                ProjectInfoDTO info = new ProjectInfoDTO();
                //绑定项目信息
                info.ProjectId = proj.Id;
                info.ProjectName = proj.Name;
                //绑定设计清单信息
                var DesignList = designs.Where(s => s.ProjectId.Equals(proj.Id)).OrderByDescending(s => s.CreateTime).ToList();
                //绑定分拣数据信息
                var SortingList = SortingLists.Where(s => s.ProjectId.Equals(proj.Id)).OrderByDescending(s => s.SortingDate).ToList();
                var Rerutencount = rerurncount.IsNullOrEmpty() ? null : rerurncount.Where(s => s.ProjectId.Equals(proj.Id)).FirstOrDefault();
                foreach (var design in DesignList)
                {
                    //添加设计清单汇总物料数量
                    DesignMaterialCountDTO designMaterialCount = new DesignMaterialCountDTO();
                    designMaterialCount.DesignId = design.DesignListId;
                    designMaterialCount.Num = Convert.ToDouble(design.RealNum);

                    designMaterialCounts.Add(designMaterialCount);

                    design.DeliveryNotice = notices.Where(s => s.DesignId.Equals(design.DesignListId)).FirstOrDefault();
                    design.InspectionInfo = InspectionInfo.Where(m => m.BuildingnumberId.Equals(design.BuildingNumberID)).FirstOrDefault();
                    //获取清单对应楼号回货数据
                    design.ReturnGoods = returnGoods.Where(m => m.BuildingId.Equals(design.BuildingNumberID)).ToList();
                    if (!design.DeliveryNotice.IsNullOrEmpty())
                    {
                        design.DeliveryNotice.PickUpInfos = pickupinfo.Where(s => s.DeliveryNoticeItemId.Equals(design.DeliveryNotice.DeliveryNoticeItemId)).OrderByDescending(s => s.CreateTime).ToList();
                        if (!design.DeliveryNotice.PickUpInfos.IsNullOrEmpty())
                        {
                            foreach (var pinfo in design.DeliveryNotice.PickUpInfos)
                            {
                                pinfo.TransportBills = transports.Where(s => s.PickUpId.Equals(pinfo.Id)).ToList();
                                //添加设计清单装车物料数量汇总
                                //TransportBillCountDTO transportBillCount = new TransportBillCountDTO();
                                //transportBillCount.DesignId = design.DesignListId;
                                //transportBillCount.Num = Convert.ToDouble(pinfo.TransportBills.Sum(s => s.NetloadNumber));
                                //transportBillCounts.Add(transportBillCount);
                            }
                        }
                    }
                    //添加设计清单装车物料重量(吨)汇总 牟俊明 2022.7.27
                    TransportBillCountDTO transportBillCount = new TransportBillCountDTO();
                    transportBillCount.DesignId = design.DesignListId;

                    transportBillCount.Num = getTransportBillWeightByDesignId(design.DesignListId);
                    transportBillCounts.Add(transportBillCount);

                    double descount = Convert.ToDouble(designMaterialCounts.FirstOrDefault(s => s.DesignId.Equals(design.DesignListId)).Num);
                    //double desovercount = Convert.ToDouble(transportBillCounts.Where(s => s.DesignId.Equals(design.DesignListId)).Sum(s => s.Num));
                    double desovercount = transportBillCount.Num;

                    //double descount = Convert.ToDouble(designMaterialCounts.FirstOrDefault(s => s.DesignId.Equals(design.DesignListId)).Num);
                    //double desovercount = Convert.ToDouble(transportBillCounts.Where(s => s.DesignId.Equals(design.DesignListId)).Sum(s => s.Num));

                    design.BuildingDeliveryRate = descount == 0 ? "0":Math.Round(((desovercount / descount) * 100), MidpointRounding.AwayFromZero).ToString();
                 }
                info.DesignLists = DesignList;
                info.SortingList = SortingList;
                double objcount = Convert.ToDouble(designMaterialCounts.Sum(s => s.Num));
                double overcount = Convert.ToDouble(transportBillCounts.Sum(s => s.Num));
                double Returncount = 0;
                if (!Rerutencount.IsNullOrEmpty())
                {
                    Returncount = Convert.ToDouble(Rerutencount.TotalReturns);
                }
                info.ProjectDeliveryRatio = objcount == 0 ? "0" : Math.Round(((overcount / objcount) * 100), MidpointRounding.AwayFromZero).ToString();
                info.ProjectReturnRatio = overcount == 0 ? "0" : Math.Round(((Returncount / overcount) * 100), MidpointRounding.AwayFromZero).ToString();
                dto.Add(info);
            }
            return new PageResult<ProjectInfoDTO> { Data = dto, Total = dto.Count };

        }

        /// <summary>
        /// 大屏项目进度汇总(vue自定页面)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<ProjectInfoDTO>> GetProjectInfoDataListBySearchAsync(PageInput<ConditionDTO> input)
        {
            List<ProjectInfoDTO> dto = new List<ProjectInfoDTO>();
            //根据项目热度获取项目

            var q = GetIQueryable();
            var where = LinqHelper.True<PB_Project>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_Project, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }

            var projs = await q.Where(where).GetPageListAsync(input);
            if (projs.Count == 0)
            {
                //projs = await getProjectLimitAsync();
                throw new Exception("暂无数据");
            }
            var designs = await getDesignListByProjectAsync(projs);
            var InspectionInfo = await getInspectionAsync();
            var notices = await getDeliveryNoticesByDesignIdAsync(designs);
            var pickupinfo = await getPickUpInfoByDeliveryNoticesItemIdAsync(notices);
            var SortingLists = await getSortingListByProjectAsync(projs);
            var transports = await getTransportBillByPickUpIdAsync(pickupinfo);
            var PB_ProjectLog = await Db.GetIQueryable<PB_ProjectStatuLog>().Where(m => m.ProjectId == search.Keyword).OrderByDescending(m => m.CreateTime).FirstOrDefaultAsync();
            var returnGoods = await getReturnGoodsByBuildingnumberIdAsync(designs);
            var rerurncount = await GetReurnMaterialCounts(projs);

            //获取分拣数据
            string pbSortingsql = "SELECT IFNULL(SUM(a.SortingSum), 0) AS SortingNums, b.RealName AS SortingName,MAX(a.CreateTime) as SortingDate   FROM PB_ReturnSortDetail as a LEFT JOIN Base_User as b on a.CreatorId = b.Id  WHERE ProjectId = '" + search.Keyword + "' GROUP BY a.CreatorId";
         
            string StateLogListsql = "SELECT  * FROM( SELECT    b.Buildingnumber AS Buildingnumber,a.*,	k.RealName, ROW_NUMBER() over(PARTITION BY a.ProjectId,a.BuildingnumberId ORDER BY a.CreateTime DESC) AS rowsid FROM  PB_ProjectStatuLog as a LEFT JOIN PB_Buildingnumber as b ON  a.BuildingnumberId = b.Id LEFT JOIN PB_Project as c ON a.ProjectId = c.Id LEFT JOIN Base_User as u ON b.TechnicalDirector = u.Id LEFT JOIN Base_EnumItem as e on c.Businesstype = e.`Value` LEFT JOIN Base_User as k on a.CreatorId=k.Id ) AS t WHERE rowsid = 1  AND t.ProjectId = '" + search.Keyword + "'";
            List<PB_ProjectStateLogList> ProjectStateLogList = await Db.GetListBySqlAsync<PB_ProjectStateLogList>(StateLogListsql);

            foreach (var proj in projs)
            {
                //设计清单下辖物料数量汇总
                List<DesignMaterialCountDTO> designMaterialCounts = new List<DesignMaterialCountDTO>();
                //设计清单下装车物料数量汇总
                List<TransportBillCountDTO> transportBillCounts = new List<TransportBillCountDTO>();
                ProjectInfoDTO info = new ProjectInfoDTO();
                //绑定项目信息
                info.ProjectId = proj.Id;
                info.ProjectName = proj.Name;
                info.Describe = PB_ProjectLog.ToJson();
                //绑定回货数据
                //info.ReturnList = ReturnList;
                var SortingList = SortingLists.Where(s => s.ProjectId.Equals(proj.Id)).OrderByDescending(s => s.SortingDate).ToList();
                //获取最新项目提升数据
                info.ProjectStateLogList = ProjectStateLogList;
                //绑定设计清单信息
                var DesignList = designs.Where(s => s.ProjectId.Equals(proj.Id)).OrderByDescending(s => s.CreateTime).ToList();
                var Rerutencount = rerurncount.IsNullOrEmpty() ? null : rerurncount.Where(s => s.ProjectId.Equals(proj.Id)).FirstOrDefault();
                foreach (var design in DesignList)
                {
                    //添加设计清单汇总物料数量
                    DesignMaterialCountDTO designMaterialCount = new DesignMaterialCountDTO();
                    designMaterialCount.DesignId = design.DesignListId;
                    designMaterialCount.Num = Convert.ToDouble(design.RealNum);
                    designMaterialCounts.Add(designMaterialCount);

                    design.DeliveryNotice = notices.Where(s => s.DesignId.Equals(design.DesignListId)).FirstOrDefault();
                    design.InspectionInfo = InspectionInfo.Where(m => m.BuildingnumberId.Equals(design.BuildingNumberID)).FirstOrDefault();
                    //获取回货数据
                    design.ReturnGoods = returnGoods.Where(m => m.BuildingId.Equals(design.BuildingNumberID)).ToList();
                    if (!design.DeliveryNotice.IsNullOrEmpty())
                    {
                        design.DeliveryNotice.PickUpInfos = pickupinfo.Where(s => s.DeliveryNoticeItemId.Equals(design.DeliveryNotice.DeliveryNoticeItemId)).OrderByDescending(s => s.CreateTime).ToList();
                        if (!design.DeliveryNotice.PickUpInfos.IsNullOrEmpty())
                        {
                            foreach (var pinfo in design.DeliveryNotice.PickUpInfos)
                            {
                                pinfo.TransportBills = transports.Where(s => s.PickUpId.Equals(pinfo.Id)).ToList();
                                //添加设计清单装车物料数量汇总
                                TransportBillCountDTO transportBillCount = new TransportBillCountDTO();
                                transportBillCount.DesignId = design.DesignListId;
                                transportBillCount.Num = Convert.ToDouble(pinfo.TransportBills.Sum(s => s.LoadingCount));
                                transportBillCounts.Add(transportBillCount);
                            }
                        }
                    }
                    double descount = Convert.ToDouble(designMaterialCounts.FirstOrDefault(s => s.DesignId.Equals(design.DesignListId)).Num);
                    double desovercount = Convert.ToDouble(transportBillCounts.Where(s => s.DesignId.Equals(design.DesignListId)).Sum(s => s.Num));
                    design.BuildingDeliveryRate = Math.Round(((desovercount / descount) * 100), MidpointRounding.AwayFromZero).ToString();
                }
                info.DesignLists = DesignList;
                info.SortingList = SortingList;
                double objcount = Convert.ToDouble(designMaterialCounts.Sum(s => s.Num));
                double overcount = Convert.ToDouble(transportBillCounts.Sum(s => s.Num));
                double Returncount = 0;
                if (!Rerutencount.IsNullOrEmpty())
                {
                    Returncount = Convert.ToDouble(Rerutencount.TotalReturns);
                }
                var DesignGrossWeight = await GetD(search.Keyword);
                //已发数量总重
                var Totalshippingweight = await GetD2(search.Keyword);
                //已回数量总重
                var ReturnRadate = await GetD3(search.Keyword);
                info.ProjectDeliveryRatio = Totalshippingweight.Count == 0 ? "0" : Math.Round((Convert.ToDouble(Totalshippingweight[0].GrossWeightDelivered) / Convert.ToDouble(DesignGrossWeight[0].OtalWeightOfList)) * 100, 2).ToString();
                info.ProjectReturnRatio = ReturnRadate.Count == 0 ? "0" : Math.Round((Convert.ToDouble(ReturnRadate[0].ReturnRate) / Convert.ToDouble(Totalshippingweight[0].GrossWeightDelivered)) * 100, 2).ToString();
                dto.Add(info);
            }
            return new PageResult<ProjectInfoDTO> { Data = dto, Total = dto.Count };

        }
        /// <summary>
        /// 根据操作热度获取项目
        /// </summary>
        /// <returns></returns>
        private async Task<List<PB_Project>> getProjectAsync()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("a.Id,");
            sql.Append("a.DeptId,");
            sql.Append("a.Code,");
            sql.Append("a.Name,");
            sql.Append("a.Abbreviation,");
            sql.Append("a.Remarks,");
            sql.Append("b.OptionTime CreateTime,");
            sql.Append("a.CreatorId,");
            sql.Append("a.Deleted,");
            sql.Append("a.ContactsId,");
            sql.Append("a.ProvinceId,");
            sql.Append("a.CityId,");
            sql.Append("a.AreaId,");
            sql.Append("a.Address,");
            sql.Append("a.ProjectImg,");
            sql.Append("a.Businesstype,");
            sql.Append("a.Purchaseandsalesunit,");
            sql.Append("a.Cooperativeunit,");
            sql.Append("a.Signingunit,");
            sql.Append("a.State ");
            sql.Append("FROM ");
            sql.Append("PB_Project a ");
            sql.Append("INNER JOIN ( SELECT ProjectId, max( CreateTime ) OptionTime FROM PB_ProjectProcess GROUP BY ProjectId ) b ON a.Id = b.ProjectId ");
            sql.Append("WHERE to_days(b.OptionTime) = to_days(now())");
            sql.Append("ORDER BY ");
            sql.Append("b.OptionTime DESC ");

            return await Db.GetListBySqlAsync<PB_Project>(sql.ToString());
        }
        private async Task<List<PB_Project>> getProjectLimitAsync()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("a.Id,");
            sql.Append("a.DeptId,");
            sql.Append("a.Code,");
            sql.Append("a.Name,");
            sql.Append("a.Abbreviation,");
            sql.Append("a.Remarks,");
            sql.Append("b.OptionTime CreateTime,");
            sql.Append("a.CreatorId,");
            sql.Append("a.Deleted,");
            sql.Append("a.ContactsId,");
            sql.Append("a.ProvinceId,");
            sql.Append("a.CityId,");
            sql.Append("a.AreaId,");
            sql.Append("a.Address,");
            sql.Append("a.ProjectImg,");
            sql.Append("a.Businesstype,");
            sql.Append("a.Purchaseandsalesunit,");
            sql.Append("a.Cooperativeunit,");
            sql.Append("a.Signingunit,");
            sql.Append("a.State ");
            sql.Append("FROM ");
            sql.Append("PB_Project a ");
            sql.Append("INNER JOIN ( SELECT ProjectId, max( CreateTime ) OptionTime FROM PB_ProjectProcess GROUP BY ProjectId ) b ON a.Id = b.ProjectId ");
            sql.Append("ORDER BY ");
            sql.Append("b.OptionTime DESC ");
            sql.Append("LIMIT 30 ");

            return await Db.GetListBySqlAsync<PB_Project>(sql.ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Projects"></param>
        /// <returns></returns>
        private async Task<List<ReurnMaterialCount>> GetReurnMaterialCounts(List<PB_Project> Projects)
        {
            if (Projects.Count < 1)
            {
                return new List<ReurnMaterialCount>();
            }
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("select");
            sql.AppendLine("IFNULL(sum(ThisReturnNums),0) 'TotalReturns',");
            sql.AppendLine("ProjectId ");
            sql.AppendLine("from PB_ReturnDetailLog");
            sql.AppendLine("WHERE ");
            sql.AppendLine("ProjectId IN(");
            string whereSql = "";
            foreach (var project in Projects)
            {
                whereSql += "'" + project.Id + "',";
            }
            sql.AppendLine(whereSql.Substring(0, whereSql.Length - 1));
            sql.AppendLine(") ");
            sql.AppendLine("GROUP BY");
            sql.AppendLine("ProjectId");
            List<ReurnMaterialCount> reurnMaterialCounts = await Db.GetListBySqlAsync<ReurnMaterialCount>(sql.ToString());
            if (reurnMaterialCounts.Count == 0)
            {
                return null;
            }
            return reurnMaterialCounts;

        }
        /// <summary>
        /// 按照项目汇总回货重量(吨)
        /// </summary>
        /// <param name="Projects"></param>
        /// <returns></returns>
        private async Task<List<ReurnMaterialCount>> GetReurnMaterialWeights(List<PB_Project> Projects)
        {
            if (Projects.Count < 1)
            {
                return new List<ReurnMaterialCount>();
            }
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("select");
            sql.AppendLine("IFNULL(sum(r.ThisReturnNums*IFNULL(m.Weight,0))/1000,0) 'TotalReturns',");
            sql.AppendLine("ProjectId ");
            sql.AppendLine("from PB_ReturnDetailLog r ");
            sql.AppendLine("LEFT JOIN PB_Material m ON r.MaterialId=m.Id ");
            sql.AppendLine("WHERE ");
            sql.AppendLine("ProjectId IN(");
            string whereSql = "";
            foreach (var project in Projects)
            {
                whereSql += "'" + project.Id + "',";
            }
            sql.AppendLine(whereSql.Substring(0, whereSql.Length - 1));
            sql.AppendLine(") ");
            sql.AppendLine("GROUP BY");
            sql.AppendLine("ProjectId");
            List<ReurnMaterialCount> reurnMaterialCounts = await Db.GetListBySqlAsync<ReurnMaterialCount>(sql.ToString());
            if (reurnMaterialCounts.Count == 0)
            {
                return null;
            }
            return reurnMaterialCounts;

        }
        /// <summary>
        /// 获取设计清单列表
        /// </summary>
        /// <param name="projs"></param>
        /// <returns></returns>
        private async Task<List<DesignList>> getDesignListByProjectAsync(List<PB_Project> projs)
        {
            if (projs.Count < 1)
            {
                return new List<DesignList>();
            }
            #region
            //StringBuilder sql = new StringBuilder();
            //sql.AppendLine("SELECT");
            //sql.AppendLine("pro.*,");
            //sql.AppendLine("prosl.CreateTime AS InspectionTime,");
            //sql.AppendLine("prosl.RealName AS InspectorName,");
            //sql.AppendLine("CASE");
            //sql.AppendLine(" prosl.`Status` ");
            //sql.AppendLine(" WHEN 0 THEN");
            //sql.AppendLine(" '提升'");
            //sql.AppendLine(" WHEN 1 THEN");
            //sql.AppendLine(" '搭设'");
            //sql.AppendLine(" WHEN 2 THEN");
            //sql.AppendLine(" '拆除' ELSE NULL");
            //sql.AppendLine("END AS STATUS,");
            //sql.AppendLine("prosl.NumberLayers");
            //sql.AppendLine("   FROM");
            //sql.AppendLine("(");
            //sql.AppendLine("SELECT");
            //sql.AppendLine(" a.ProjectId,");
            //sql.AppendLine(" a.Id DesignListId,");
            //sql.AppendLine(" a.NAME DesignListName,");
            //sql.AppendLine(" a.BuildingNumberID BuildingNumberID,");
            //sql.AppendLine(" a.CreateTime,");
            //sql.AppendLine(" ifnull(b.RealName, '') CreatorName,");
            //sql.AppendLine(" c.BuildingNumber,");
            //sql.AppendLine(" ifnull(d.RealNums, 0) RealNum");
            //sql.AppendLine("FROM");
            //sql.AppendLine(" PB_DesignList a");
            //sql.AppendLine(" LEFT JOIN Base_User b ON a.CreatorId = b.Id");
            //sql.AppendLine(" LEFT JOIN PB_Buildingnumber c ON a.BuildingNumberID = c.Id");
            //sql.AppendLine(" LEFT JOIN(");
            //sql.AppendLine(" SELECT");
            //sql.AppendLine("  a.ParentId,");
            //sql.AppendLine("  sum(ifnull(b.Weight, 0) / 1000 * a.RealNums) RealNums");
            //sql.AppendLine(" FROM");
            //sql.AppendLine("  PB_DesignDetail a");
            //sql.AppendLine("  LEFT JOIN PB_Material b ON a.MaterialId = b.Id");
            ////只计算基础设计详单，而派车列表没有排除补加。所以排除此限制
            ////sql.AppendLine(" WHERE");
            ////sql.AppendLine("  a.State = 0");

            //sql.AppendLine(" GROUP BY");
            //sql.AppendLine("  a.ParentId");
            //sql.AppendLine(" ) d ON a.Id = d.ParentId");
            //sql.AppendLine("WHERE");
            //sql.AppendLine(" a.ExamineStatus = 2");
            //sql.AppendLine(" AND a.ProjectId IN(");
            //string whereSql = "";
            //foreach (var project in projs)
            //{
            //    whereSql += "'" + project.Id + "',";
            //}
            //sql.AppendLine(whereSql.Substring(0, whereSql.Length - 1));
            //sql.AppendLine(") ");
            //sql.AppendLine("order by a.CreateTime");
            //sql.AppendLine(") AS pro");
            //sql.AppendLine("LEFT JOIN(");
            //sql.AppendLine("SELECT");
            //sql.AppendLine(" a.ProjectId,");
            //sql.AppendLine(" a.BuildingnumberId,");
            //sql.AppendLine(" b.RealName,");
            //sql.AppendLine(" a.CreateTime,");
            //sql.AppendLine(" a.NumberLayers,");
            //sql.AppendLine(" a.`Status` ");
            //sql.AppendLine("FROM");
            //sql.AppendLine(" PB_ProjectStatuLog AS a");
            //sql.AppendLine(" LEFT JOIN Base_User b ON a.CreatorId = b.Id");
            //sql.AppendLine("   WHERE");
            //sql.AppendLine("  ProjectId IN(");
            //sql.AppendLine(whereSql.Substring(0, whereSql.Length - 1));
            //sql.AppendLine(") ");
            //sql.AppendLine("ORDER BY");
            //sql.AppendLine(" CreateTime DESC");
            //sql.AppendLine("LIMIT 1");
            //sql.AppendLine(") AS prosl ON pro.BuildingNumberID = prosl.BuildingnumberId");
            #endregion
            List<string> projectids = new List<string>();
            foreach (var item in projs)
            {
                projectids.Add(item.Id);
            }

            string sql = $@"SELECT
pro.*,
prosl.CreateTime AS InspectionTime,
prosl.RealName AS InspectorName,
CASE
		prosl.`Status` 
		WHEN 0 THEN
		'提升' 
		WHEN 1 THEN
		'搭设' 
		WHEN 2 THEN
		'拆除' ELSE NULL 
	END AS STATUS,
	prosl.NumberLayers 
FROM
	(
select 
a.ProjectId,
		a.Id DesignListId,
		a.`Name` DesignListName,
		a.BuildingNumberID BuildingNumberID,
		a.CreateTime,
		ifnull( b.RealName, '' ) CreatorName,
		c.BuildingNumber,
		sum( ifnull( m.Weight, 0 ) / 1000 * d.RealNums ) RealNums 
 from pb_designlist as a
LEFT JOIN Base_User b ON a.CreatorId = b.Id
LEFT JOIN PB_Buildingnumber c ON a.BuildingNumberID = c.Id
left join pb_designdetail as d on a.Id=d.ParentId
left join pb_material as m on d.MaterialId=m.Id
where a.ProjectId in ('{string.Join("','",projectids.ToArray())}') and a.ExamineStatus=2
group by a.ProjectId,
		a.Id,
		a.`Name`,
		a.BuildingNumberID,
		a.CreateTime,
		b.RealName,
		c.BuildingNumber
		) AS pro
	LEFT JOIN (
	SELECT
		a.ProjectId,
		a.BuildingnumberId,
		b.RealName,
		a.CreateTime,
		a.NumberLayers,
		a.`Status` 
	FROM
		PB_ProjectStatuLog AS a
		LEFT JOIN Base_User b ON a.CreatorId = b.Id 
	WHERE
		ProjectId  in ('{string.Join("','", projectids.ToArray())}')
	ORDER BY
		CreateTime DESC 
	LIMIT 1 
	) AS prosl ON pro.BuildingNumberID = prosl.BuildingnumberId ";
            return await Db.GetListBySqlAsync<DesignList>(sql);
        }


        /// <summary>
        /// 根据指定楼号获取最新巡检记录
        /// </summary>
        /// <param name="BuildingNumberId"></param>
        /// <returns></returns>
        private async Task<List<InspectionInfo>> getInspectionAsync()
        {

            //修改Status空值显示，牟俊明，2022.7.28
            string sql = $"SELECT b.RealName InspectorName,a.BuildingnumberId,a.CreateTime InspectionTime,a.NumberLayers,c.Buildingnumber,CASE a.`Status` WHEN 0 THEN '提升' WHEN 1 THEN '搭设' WHEN 2 THEN '拆除' ELSE '停止' END AS Status FROM PB_ProjectStatuLog AS a LEFT JOIN Base_User b ON a.CreatorId = b.Id  LEFT JOIN PB_Buildingnumber AS c on a.BuildingnumberId = c.Id ORDER BY a.CreateTime DESC ";
            return await Db.GetListBySqlAsync<InspectionInfo>(sql);
        }
        /// <summary>
        /// 获取发货通知单列表
        /// </summary>
        /// <param name="des"></param>
        /// <returns></returns>
        private async Task<List<DeliveryNotice>> getDeliveryNoticesByDesignIdAsync(List<DesignList> des)
        {
            if (des.Count < 1)
            {
                return new List<DeliveryNotice>();
            }
            StringBuilder sql = new StringBuilder();
            sql.Append("select ");
            sql.Append("a.Id DeliveryNoticeItemId,");
            sql.Append("a.DesignId,");
            sql.Append("a.ParentId DeliveryNoticeId,");
            sql.Append("a.CreateTime,");
            sql.Append("b.RealName CreatorName,");
            sql.Append("a.BuildingNo BuildingNumber ");
            sql.Append("from PB_DeliveryNoticeItem a ");
            sql.Append("left join Base_User b on a.CreatorId=b.Id ");
            sql.Append("where ");
            sql.Append("a.DesignId in (");
            string whereSql = "";
            foreach (var design in des)
            {
                whereSql += "'" + design.DesignListId + "',";
            }
            sql.Append(whereSql.Substring(0, whereSql.Length - 1));
            sql.Append(") ");
            sql.Append("order by a.CreateTime desc");
            return await Db.GetListBySqlAsync<DeliveryNotice>(sql.ToString());
        }
        /// <summary>
        /// 获取清单对应的楼号回货数据
        /// </summary>
        /// <param name="des"></param>
        /// <returns></returns>
        private async Task<List<ReturnGood>> getReturnDetailLogByDesignIdAsync(List<DesignList> des)
        {
            if (des.Count < 1)
            {
                return new List<ReturnGood>();
            }
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("IFNULL(SUM(A.ThisReturnNums), 0 ) 'ReturnCount', ");
            sql.Append(" date(	A.CreateTime) 'ReturnTime', ");
            sql.Append("B.RealName AS 'ReturnName',  ");
            sql.Append("FROM ");
            sql.Append("PB_ReturnDetailLog AS A ");
            sql.Append("LEFT JOIN Base_User AS B ON A.CreatorId = B.Id ");
            sql.Append("WHERE");
            sql.Append("A.BuildingId in (");
            string whereSql = "";
            foreach (var design in des)
            {
                whereSql += "'" + design.BuildingNumberID + "',";
            }
            sql.Append(whereSql.Substring(0, whereSql.Length - 1));
            sql.Append(") ");
            sql.Append("GROUP BY ");
            sql.Append("date( A.CreateTime), ");
            sql.Append("ORDER BY date( A.CreateTime) DESC ");
            return await Db.GetListBySqlAsync<ReturnGood>(sql.ToString());
        }

        /// <summary>
        /// 获取备货单信息
        /// </summary>
        /// <param name="del"></param>
        /// <returns></returns>
        private async Task<List<PickUpInfo>> getPickUpInfoByDeliveryNoticesItemIdAsync(List<DeliveryNotice> del)
        {
            if (del.Count < 1)
            {
                return new List<PickUpInfo>();
            }
            //StringBuilder sql = new StringBuilder();
            ////sql.Append("select ");
            ////sql.Append("a.Id,");
            ////sql.Append("a.ParentId DeliveryNoticeItemId,");
            ////sql.Append("a.CreateTime,");
            ////sql.Append("b.RealName CreatorName,");
            ////sql.Append("c.PickUpNumber ");
            ////sql.Append("FROM PB_PickUpMain a ");
            ////sql.Append("LEFT JOIN Base_User b ON a.CreatorId = b.Id ");
            ////sql.Append("LEFT JOIN (SELECT ParentId, sum( ReadyNums ) PickUpNumber FROM PB_PickUpList GROUP BY ParentId ) c ON a.Id = c.ParentId ");

            //sql.Append("SELECT ");
            //sql.Append("a.Id,");
            //sql.Append("a.ParentId DeliveryNoticeItemId,");
            //sql.Append("a.CreateTime,");
            //sql.Append("ifnull(c.CreatorRealName, '') CreatorName,");
            //sql.Append("ifnull(c.PickUpNumber, 0) PickUpNumber ");
            //sql.Append("FROM ");
            //sql.Append("PB_PickUpMain a ");
            //sql.Append("INNER JOIN(");
            //sql.Append("SELECT ");
            //sql.Append("m.ParentId,");
            //sql.Append("sum(s.Nums ) PickUpNumber,");
            //sql.Append("s.CreatorRealName ");
            //sql.Append("FROM ");
            //sql.Append("PB_PickUpList m ");
            //sql.Append("INNER JOIN (SELECT ParentId, sum(Nums ) Nums, CreatorRealName FROM PB_UnLoadList GROUP BY ParentId, CreatorRealName ) s ON m.Id = s.ParentId ");
            //sql.Append("GROUP BY ");
            //sql.Append("m.ParentId,s.CreatorRealName ");
            //sql.Append(") c ON a.Id = c.ParentId ");
            //sql.Append("where ");
            //sql.Append("a.ParentId in (");
            //string whereSql = "";
            //foreach (var delivery in del)
            //{
            //    whereSql += "'" + delivery.DeliveryNoticeItemId + "',";
            //}
            //sql.Append(whereSql.Substring(0, whereSql.Length - 1));
            //sql.Append(") order by a.CreateTime desc");
            List<string> DeliveryNoticeItemIds = new List<string>();
            foreach (var item in del)
            {
                DeliveryNoticeItemIds.Add(item.DeliveryNoticeItemId);
            }
            string sql = $@"select 
						a.Id,a.ParentId as DeliveryNoticeItemId,a.CreateTime,ifnull(c.CreatorRealName, '') as CreatorName,SUM(c.Nums) as PickUpNumber
						 from pb_pickupmain as a
						left join pb_pickuplist as b on a.Id=b.ParentId
						left join pb_unloadlist as c on b.Id=c.ParentId
						where a.ParentId in ('{string.Join("','", DeliveryNoticeItemIds.ToArray())}')
						group by a.Id,a.ParentId,a.CreateTime,c.CreatorRealName";
            return await Db.GetListBySqlAsync<PickUpInfo>(sql);
        }

        /// <summary>
        /// 获取装车单信息
        /// </summary>dc
        /// <param name="pui"></param>
        /// <returns></returns>
        private async Task<List<TransportBill>> getTransportBillByPickUpIdAsync(List<PickUpInfo> pui)
        {
            if (pui.Count < 1)
            {
                return new List<TransportBill>();
            }
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("Round(ifnull(a.NetloadNumber,''),2) NetloadNumber,");
            sql.Append("ifnull( a.IntoTime,'') IntoTime,");
            sql.Append("a.PlateNO,");
            sql.Append("ifnull( b.LoadingCount, 0 ) LoadingCount,");
            sql.Append("ifnull( a.DepartureTime, '' ) DepartureTime,");
            sql.Append("ifnull( a.AcceptanceTime, '' ) ReceivingTime,");
            sql.Append("ifnull( b.ReceivingCount, 0 ) ReceivingCount,");
            sql.Append("b.PickUpId,");
            sql.Append("a.State ");
            sql.Append("FROM ");
            sql.Append("PB_TransportBill a ");
            sql.Append("LEFT JOIN ( ");
            sql.Append("SELECT ");
            sql.Append("m.ParentId,");
            sql.Append("sum( ifnull(t.Weight,0)*ifnull(m.PackNums,0) ) LoadingCount,");
            sql.Append("sum( m.ReceiptQuantity ) ReceivingCount,");
            sql.Append("s.`ParentId` PickUpId ");
            sql.Append("FROM ");
            sql.Append("PB_TransportBillDetail m ");
            //修改清单发车条件。牟。2022.7.29
            sql.Append("JOIN PB_TransportBill tb ON m.ParentId=tb.Id AND tb.BillType='Delivery' AND DATE(tb.DepartureTime)<=CURDATE() ");
            sql.Append("LEFT JOIN PB_PickUpList s ON m.PreparationNoteId = s.Id ");
            sql.Append("LEFT JOIN PB_Material t on m.MaterialId=t.Id ");
            sql.Append("GROUP BY ");
            sql.Append("m.ParentId, ");
            sql.Append("s.`ParentId` ");
            sql.Append(") b ON a.Id = b.ParentId ");
            sql.Append("WHERE a.NetloadNumber > 0 ");
            sql.Append("and b.PickUpId in (");
            string whereSql = "";
            foreach (var pickupinfo in pui)
            {
                whereSql += "'" + pickupinfo.Id + "',";
            }
            sql.Append(whereSql.Substring(0, whereSql.Length - 1));
            sql.Append(") ");
            return await Db.GetListBySqlAsync<TransportBill>(sql.ToString());
        }
        /// <summary>
        /// 获取设计清单相关派车详单中有入场时间的总重量（吨）,牟俊明，2022.7.28
        /// </summary>
        /// <param name="designId"></param>
        /// <returns></returns>
        private double getTransportBillWeightByDesignId(string designId)
        {
            double res = 0;
            //            string strSql = @"SELECT IFNULL(SUM(pb.PackNums*m.Weight)/1000,0) AS value
            //FROM PB_TransportBillDetail pb 
            //JOIN PB_Material m ON pb.MaterialId=m.Id
            //JOIN PB_TransportBill tb ON pb.ParentId=tb.Id AND tb.BillType='Delivery' AND DATE(tb.DepartureTime)<=CURDATE()
            //JOIN PB_DesignDetail dd ON pb.DesignDetailId=dd.Id
            //JOIN PB_DesignList pl ON dd.ParentId=pl.Id AND pl.Id='" + designId+"'";
            string strSql = $@"select IFNULL(SUM(pb.PackNums*m.Weight)/1000,0) AS value from PB_DesignList pl
left join PB_DesignDetail dd on pl.Id=dd.ParentId
left join pb_material as m on dd.MaterialId=m.Id
left join PB_TransportBillDetail as pb on dd.Id=pb.DesignDetailId
left join PB_TransportBill as tb on pb.ParentId=tb.Id AND tb.BillType='Delivery' AND DATE(tb.DepartureTime)<=CURDATE()
where pl.Id='{designId}'";
            res = Db.GetListBySqlAsync<ScreenDisplayValueDTO>(strSql).Result.FirstOrDefault().value;
            return res;
        }

        /// <summary>
        /// 根据设计清单中的项目楼号ID查询对应回货信息列表
        /// </summary>
        /// <param name="des"></param>
        /// <returns></returns>
        private async Task<List<ReturnGood>> getReturnGoodsByBuildingnumberIdAsync(List<DesignList> des)
        {

            if (des.Count < 1)
            {
                return new List<ReturnGood>();
            }
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("IFNULL(SUM(A.ThisReturnNums), 0 ) 'ReturnCount', ");
            sql.Append("C.CreateTime 'ReturnTime', ");
            sql.Append("B.RealName AS 'ReturnName',  ");
            sql.Append("A.BuildingId 'BuildingId'   ");
            sql.Append("FROM ");
            sql.Append("PB_ReturnDetailLog AS A ");
            sql.Append("LEFT JOIN Base_User AS B ON A.CreatorId = B.Id  ");
            sql.Append("LEFT JOIN PB_Return AS C ON A.ParentId=C.Id  ");
            sql.Append("WHERE ");
            sql.Append("A.BuildingId in (");
            string whereSql = "";
            foreach (var design in des)
            {
                whereSql += "'" + design.BuildingNumberID + "',";
            }
            sql.Append(whereSql.Substring(0, whereSql.Length - 1));
            sql.Append(") ");
            sql.Append("GROUP BY ");
            sql.Append("B.RealName, ");
            sql.Append("A.BuildingId, ");
            sql.Append("C.Id ");
            sql.Append("ORDER BY C.CreateTime DESC  ");
            return await Db.GetListBySqlAsync<ReturnGood>(sql.ToString());
        }
        /// <summary>
        /// 通过项目id获取分拣数据
        /// </summary>
        /// <param name="projs"></param>
        /// <returns></returns>
        private async Task<List<SortingDTO>> getSortingListByProjectAsync(List<PB_Project> projs)
        {
            if (projs.Count < 1)
            {
                return new List<SortingDTO>();
            }
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT");
            sql.AppendLine("IFNULL(Sum(a.SortingSum), 0) 'SortingNums',");
            sql.AppendLine("b.RealName 'SortingName',");
            sql.AppendLine("c.CreateTime 'SortingDate', ");
            sql.AppendLine("a.ProjectId 'ProjectId'");
            sql.AppendLine("FROM");
            sql.AppendLine("PB_ReturnSortDetail AS a");
            sql.AppendLine("LEFT JOIN PB_ReturnSorting AS c on a.ParentId=c.ID ");
            sql.AppendLine("LEFT JOIN Base_User AS b ON a.CreatorId = b.Id");
            sql.AppendLine("WHERE");
            sql.AppendLine("a.ProjectId IN(");
            string whereSql = "";
            foreach (var project in projs)
            {
                whereSql += "'" + project.Id + "',";
            }
            sql.AppendLine(whereSql.Substring(0, whereSql.Length - 1));
            sql.AppendLine(") ");
            sql.AppendLine("GROUP BY");
            sql.AppendLine("c.ParentId,");
            sql.AppendLine("c.CreateTime,");
            sql.AppendLine("b.RealName,");
            sql.AppendLine("a.ProjectId");
            return await Db.GetListBySqlAsync<SortingDTO>(sql.ToString());
        }


        /// <summary>
        /// 获取项目信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<PB_ProjectDTO>> GetProjectDataAsync(PageInput<ConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_Project, Base_Department, Base_User, Base_Region, Base_Region, Base_Region, PB_ProjectDTO>> select = (a, b, c, d, e, f) => new PB_ProjectDTO
            {
                DepartmentName = b.Name,
                ContactsName = c.RealName,
                ProvinceName = d.Name,
                CityName = e.Name,
                AreaName = f.Name
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_User>() on a.ContactsId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_Region>() on a.ProvinceId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<Base_Region>() on a.CityId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    join f in Db.GetIQueryable<Base_Region>() on a.AreaId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f);
            if (!search.Keyword.IsNullOrEmpty())
            {
                var keyword = $"%{search.Keyword}%";
                q = q.Where(x =>
                      EF.Functions.Like(x.Code, keyword)
                      || EF.Functions.Like(x.Name, keyword));
            }

            //项目档案权限
            //q.OrderByDescending(l=>l.CreateTime);
            q = q.Where(x => _operator.Property.DeptIds.Contains(x.DeptId));
            return await q.GetPageResultAsync(input);
        }

        public async Task<PB_ProjectDTO> GetTheDataAsync(string id)
        {

            Expression<Func<PB_Project, PB_Customer, PB_Customer, PB_Customer, PB_ProjectDTO>> select = (a, b, c, d) => new PB_ProjectDTO
            {
                SaleUnitName = b.Name,
                CooperativeUnitName = c.Name,
                SignUnitName = d.Name,

            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_Customer>() on a.Purchaseandsalesunit equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Customer>() on a.Cooperativeunit equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Customer>() on a.Signingunit equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);
            q = q.Where(m => m.Id == id);
            return await q.FirstOrDefaultAsync();
            ;
        }

        [DataAddLog(UserLogType.项目管理, "Name", "项目管理")]
        [DataRepeatValidate(new string[] { "Code" }, new string[] { "项目编号" })]
        public async Task AddDataAsync(PB_Project data)
        {
            data.ContactsId = "1479357752881975296";
            await InsertAsync(data);
        }

        [DataEditLog(UserLogType.项目管理, "Name", "项目管理")]
        [DataRepeatValidate(new string[] { "Code" }, new string[] { "项目编号" })]
        public async Task UpdateDataAsync(PB_Project data)
        {
            await UpdateAsync(data);
            var BuildingnumberOpen = await Db.GetIQueryable<PB_Project>().Where(m => m.Id == data.Id && m.PatrolInspection == true).FirstOrDefaultAsync();
            if (BuildingnumberOpen != null)
            {
                string sqlupdate = "update PB_Buildingnumber set IsInspection = TRUE WHERE ProjectId = '" + BuildingnumberOpen.Id + "' ";
                await ExecuteSqlAsync(sqlupdate);
                List<DD_MessageDTO> users = new List<DD_MessageDTO>();
                List<BaseDto> userss = new List<BaseDto>();
                List<BaseDto> userss3 = new List<BaseDto>();

                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT ");
                sql.Append("a.ProjectId,");
                sql.Append("c.NAME ProjectName,");
                sql.Append("a.Id BuildingId,");
                sql.Append("a.Buildingnumber,");
                sql.Append("d.DingUserId TechnicalDirector,");
                sql.Append("a.Collaborators,");
                sql.Append("b.CreatorId, ");
                sql.Append("e.DingUserId ProjectDirector ");
                sql.Append("FROM ");
                sql.Append("PB_Buildingnumber a ");
                sql.Append("LEFT JOIN (");
                sql.Append("SELECT ");
                sql.Append("CreatorId,");
                sql.Append("ProjectId,");
                sql.Append("BuildingnumberId ");
                sql.Append("FROM ");
                sql.Append("PB_ProjectStatuLog ");
                sql.Append("WHERE ");
                sql.Append("to_days( CreateTime ) = to_days(");
                sql.Append("now())) b ON a.ProjectId = b.ProjectId ");
                sql.Append("AND a.Id = b.BuildingnumberId ");
                sql.Append("LEFT JOIN PB_Project c ON a.ProjectId = c.Id ");
                sql.Append("LEFT JOIN Base_User d ON a.TechnicalDirector = d.Id ");
                sql.Append("LEFT JOIN Base_User e ON a.ProjectDirector = e.Id ");
                sql.Append("WHERE ");
                sql.Append("a.TechnicalDirector IS NOT NULL ");
                sql.Append("AND b.CreatorId IS NULL ");
                sql.Append("AND a.ProjectId ='" + BuildingnumberOpen.Id + "' ");
                users = await Db.GetListBySqlAsync<DD_MessageDTO>(sql.ToString());

                if (users.Count > 0)
                {
                    List<string> usrs = new List<string>();
                    foreach (var item in users)
                    {
                        if (item.TechnicalDirector != null)
                        {
                            usrs.Add(item.TechnicalDirector);

                        }
                        if (item.ProjectDirector != null)
                        {
                            usrs.Add(item.ProjectDirector);
                        }
                        //项目主管 技术负责人调用
                        Util.Helper.DingInputDTO dto = new Util.Helper.DingInputDTO();
                        dto.ExecutorIds = usrs;
                        dto.Str = "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "]" + item.ProjectName + "(" + item.Buildingnumber + ")待更新进度";
                        dto.MessgUrl = "eapp:pages/progress/progress";
                        DingHelper.sendMessage(dto);
                        //分割获取协作者  跳转路径  dto.MessgUrl = "eapp:pages/index/index";
                        if (item.Collaborators != null)
                        {
                            //创建协作者跳转页
                            Util.Helper.DingInputDTO CollaboratorsDto = new Util.Helper.DingInputDTO();
                            string[] CollaboratorslIST = item.Collaborators.Split(',');
                            string _where = "";
                            foreach (var coll in CollaboratorslIST)
                            {
                                _where += "'" + coll + "',";
                            }
                            StringBuilder sql4 = new StringBuilder();
                            sql4.Append("select DingUserId  ");
                            sql4.Append("from ");
                            sql4.Append("Base_User  where id in (" + _where.Substring(0, _where.Length - 1) + ")");
                            var userss4 = await Db.GetListBySqlAsync<BaseDto>(sql4.ToString());

                            List<string> Xiezuos = new List<string>();
                            foreach (var xiezuo in userss4)
                            {
                                Xiezuos.Add(xiezuo.DingUserId);
                            }

                            CollaboratorsDto.ExecutorIds = Xiezuos;
                            CollaboratorsDto.Str = "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "]" + item.ProjectName + "(" + item.Buildingnumber + ")待更新进度";
                            CollaboratorsDto.MessgUrl = "eapp:pages/progress/progress";//协作者url
                            DingHelper.sendMessage(CollaboratorsDto);

                        }
                    }
                }

            }
            var BuildingnumberClose = await Db.GetIQueryable<PB_Project>().Where(x => x.Id == data.Id && x.PatrolInspection == false).FirstOrDefaultAsync();

            if (BuildingnumberClose != null)
            {
                string sqlupdateClose = "update PB_Buildingnumber  set IsInspection=false where ProjectId='" + BuildingnumberClose.Id + "' ";
                await ExecuteSqlAsync(sqlupdateClose);
            }
        }
        [DataDeleteLog(UserLogType.项目管理, "Name", "项目管理")]
        public async Task DeleteDataAsync(List<string> ids)
        {
            if (await Db.GetIQueryable<PB_Buildingnumber>().AnyAsync(m => ids.Contains(m.ProjectId)))
            {
                throw new BusException("删除失败！该项目下有楼栋信息禁止删除!");
            }

            await DeleteAsync(ids);
        }

        public async Task<List<TreeDataDTO>> GetDataListInfo()
        {
            Expression<Func<PB_Project, PB_DesignList, PB_ProjectTreeDTO>> select = (a, b) => new PB_ProjectTreeDTO
            {
                key = b.Id,//清单id
                title = b.Name,//清单名称

            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_DesignList>() on a.Id equals b.ProjectId
                    //into ab from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);

            var res = await q.ToListAsync();

            List<TreeDataDTO> dataTypeInfos = new List<TreeDataDTO>();
            List<IGrouping<string, PB_ProjectTreeDTO>> fenzu = res.ToList().GroupBy(P => P.Name).ToList();
            int i = 0;
            foreach (IGrouping<string, PB_ProjectTreeDTO> item in fenzu)
            {
                TreeDataDTO dataTypeInfo = new TreeDataDTO();
                dataTypeInfo.key = i++.ToString();
                dataTypeInfo.title = item.Key;
                dataTypeInfo.children = item.ToList();
                dataTypeInfos.Add(dataTypeInfo);
            }
            return dataTypeInfos;
        }

        public async Task<List<TreeDataDTO>> GetDataMainListInfo(string obiectName)
        {
            StringBuilder sbjson = new StringBuilder();
            var q = GetIQueryable();
            if (!string.IsNullOrEmpty(obiectName))
            {
                q = q.Where(m => m.Name.Contains(obiectName));
            }
            q = q.Where(m => _operator.Property.DeptIds.Contains(m.DeptId) && m.Deleted == false && m.State == 0);
            var list = await q.GetPageListAsync(new PageInput());
            List<TreeDataDTO> dataTypeInfos = new List<TreeDataDTO>();
            foreach (var item in list)
            {
                TreeDataDTO dataTypeInfo = new TreeDataDTO();
                dataTypeInfo.key = item.Id;
                dataTypeInfo.title = item.Name;
                dataTypeInfos.Add(dataTypeInfo);
            }
            return dataTypeInfos;
        }

        public async Task<List<TreeDataStockDTO>> GetdeliverGoodsListInfo(string ObjectName)
        {
            Expression<Func<PB_DeliveryNotice, PB_DeliveryNoticeItem, PB_Project, PB_DesignList, PB_Buildingnumber, PB_DesignListType, PB_ProjectTreeDTOIn>> select = (a, b, c, d, e, f) => new PB_ProjectTreeDTOIn
            {
                key = b.Id,//发货通知单ID
                title = c.Name + e.Buildingnumber + f.Name,//摘要
                ProjectName = a.ProjectName,
                AuditStatus = a.AuditStatus,
                State=c.State,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_DeliveryNotice>().AsExpandable()
                    join b in Db.GetIQueryable<PB_DeliveryNoticeItem>() on a.Id equals b.ParentId
                    join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id
                    join d in Db.GetIQueryable<PB_DesignList>() on b.DesignId equals d.Id
                    join e in Db.GetIQueryable<PB_Buildingnumber>() on d.BuildingNumberID equals e.Id
                    join f in Db.GetIQueryable<PB_DesignListType>() on d.Type equals f.Id
                    select @select.Invoke(a, b, c, d, e, f);
            if (!string.IsNullOrEmpty(ObjectName))
            {
                q = q.Where(m => m.ProjectName.Contains(ObjectName));
            }
            q = q.Where(m=>m.State==0);
            var res = await q.Where(P => P.AuditStatus == 1 || P.AuditStatus == 3).ToListAsync();

            List<TreeDataStockDTO> dataTypeInfos = new List<TreeDataStockDTO>();
            List<IGrouping<string, PB_ProjectTreeDTOIn>> GroupbyInfo = res.ToList().GroupBy(P => P.ProjectName).ToList();
            int i = 0;
            foreach (IGrouping<string, PB_ProjectTreeDTOIn> item in GroupbyInfo)
            {
                TreeDataStockDTO dataTypeInfo = new TreeDataStockDTO();
                dataTypeInfo.key = i++.ToString();
                dataTypeInfo.title = item.Key;
                dataTypeInfo.children = item.ToList();
                dataTypeInfos.Add(dataTypeInfo);
            }
            return dataTypeInfos;
        }

        /// <summary>
        /// 获取当前项目回货数据
        /// </summary>
        /// <param name="input">ProjectId</param>
        /// <returns></returns>
        public async Task<PageResult<EndReturnNums>> GetProjectListDataAsync(PageInput<EndReturnConditionDTO> input)
        {
            string sql = $"select m.`Code` MaterialCode,m.`Name` MaterialName,m.Spec MateriaSpec, a.Nums Nums, IFNULL(c.ReturnNums, 0) ReturnNums from PB_DesignDetail as a LEFT JOIN PB_DesignList AS b on a.ParentId = b.Id LEFT JOIN PB_Material as m on a.MaterialId = m.Id LEFT JOIN PB_ReturnDetailLog AS c ON b.ProjectId = c.ProjectId and c.MaterialId = a.MaterialId WHERE b.ProjectId = '{input.Search.ProjectId}'";
            var res = await Db.GetListBySqlAsync<EndReturnNums>(sql);
            int pagecount;//总页数
            List<EndReturnNums> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<EndReturnNums> productes = new List<EndReturnNums>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            return new PageResult<EndReturnNums> { Data = productes, Total = pagees };
        }

        // <summary>
        /// 关闭项目接口
        /// </summary>
        /// <param name="ProjectId">项目id</param>
        /// <returns></returns>
        public async Task CloseProjectDataAsync(string ProjectId)
        {
            await ExecuteSqlAsync("update PB_Project set State=1,ClosingDate='"+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +"' where Id='" + ProjectId + "'");
            await ExecuteSqlAsync("update PB_Buildingnumber set IsInspection=0 where ProjectId='" + ProjectId + "'");
            
        }

        public IQueryable<T> GetQueryable<T>() where T : class, new()
        {
            return Db.GetIQueryable<T>();
        }

        public IQueryable<T> GetQueryableOK<T>() where T : class, new()
        {
            return Db.GetIQueryable<T>();
        }

        public async Task AddDataExlAsync(List<PB_Project> list)
        {
            try
            {
                BulkInsert(list);
                //await Db.InsertAsync<PB_Project>(list);
                //return Db.InsertAsync(list);
            }
            catch (Exception ex)
            {

                throw;
            }

        }
        public int UpdatePB_Buildingnumber(string Buildingnumber, string ProjectId)
        {

            return Db.ExecuteSql("UPDATE PB_Buildingnumber SET  ProjectId='" + ProjectId + "'  WHERE Buildingnumber='" + Buildingnumber + "'");
        }

        public Task AddDataExlAsyncLou(List<PB_Buildingnumber> list)
        {
            return Db.InsertAsync(list);
        }

        /// <summary>
        /// 回货选择项目信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<PB_ProjectReturnDTO>> GetProjectReturnDataListAsync(PageInput<ProjectReturnConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_Project, PB_DeliveryNotice, PB_ProjectReturnDTO>> select = (a, b) => new PB_ProjectReturnDTO
            {
                NoticeId = b.Id,
                Type = b.Type,
                ConsigneeId = b.ConsigneeId,
                ConsigneeName = b.ConsigneeName,
                ConsigneeTelephone = b.ConsigneeTelephone
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_DeliveryNotice>() on a.Id equals b.ProjectId into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            if (!search.Keyword.IsNullOrEmpty())
            {
                var keyword = $"%{search.Keyword}%";
                q = q.Where(x =>
                      EF.Functions.Like(x.Code, keyword)
                      || EF.Functions.Like(x.Name, keyword));
            }
            //项目档案权限
            q = q.OrderByDescending(p => p.CreateTime);
            q = q.Where(x => _operator.Property.DeptIds.Contains(x.DeptId) && x.State == 0 && x.Type == 2 && x.ConsigneeId == search.UserId);
            return await q.GetPageResultAsync(input);
        }


        /// <summary>
        /// 获取项目名下物料总重量 计算发货率 
        /// </summary>
        /// <param name="ProjectId">项目id</param>
        /// <returns></returns>
        public async Task<List<ProjectDeliveryRateGroup>> GetD(string ProjectId) {
           
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT");
            sql.AppendLine("ProjectId,");
            sql.AppendLine("SUM(RealNums) AS RealNums,");
            sql.AppendLine("SUM( RealNumsWeight ) AS OtalWeightOfList");
            sql.AppendLine("FROM");
            sql.AppendLine("(");
            sql.AppendLine("SELECT");
            sql.AppendLine("P.Id ProjectId,");
            sql.AppendLine("a.MaterialId AS Id,");
            sql.AppendLine("b.`Code`,");
            sql.AppendLine("b.`Name`,");
            sql.AppendLine("b.Weight,");
            sql.AppendLine("c.`Name` AS UnitGroupName,");
            sql.AppendLine("a.Nums,");
            sql.AppendLine("a.Slot,");
            sql.AppendLine("a.RealNums,");
            sql.AppendLine("a.RealNums * b.Weight AS RealNumsWeight,");
            sql.AppendLine("a.State,");
            sql.AppendLine("a.Id AS SubId,");
            sql.AppendLine("b.IsStandard");
            sql.AppendLine("FROM");
            sql.AppendLine("PB_DesignDetail AS a");
            sql.AppendLine("JOIN PB_Material AS b ON a.MaterialId = b.Id");
            sql.AppendLine("LEFT JOIN PB_DesignList AS list ON a.ParentId = list.Id");
            sql.AppendLine("LEFT JOIN PB_Project AS P ON list.ProjectId = P.Id");
            sql.AppendLine("JOIN Base_UnitItems AS c ON b.storageId = c.Id");
            sql.AppendLine("WHERE");
            sql.AppendLine("P.Id = '"+ProjectId+"'");
            sql.AppendLine("AND a.State = 0 UNION ALL");
            sql.AppendLine("SELECT");
            sql.AppendLine("P.Id ProjectId,");
            sql.AppendLine("a.MaterialId AS Id,");
            sql.AppendLine("b.`Code`,");
            sql.AppendLine("b.`Name`,");
            sql.AppendLine("b.Weight,");
            sql.AppendLine("c.`Name` AS UnitGroupName,");
            sql.AppendLine("a.Nums,");
            sql.AppendLine("a.Slot,");
            sql.AppendLine("a.RealNums,");
            sql.AppendLine("a.RealNums * b.Weight AS RealNumsWeight,");
            sql.AppendLine("a.State,");
            sql.AppendLine("a.Id AS SubId,");
            sql.AppendLine("b.IsStandard");
            sql.AppendLine("FROM");
            sql.AppendLine("PB_DesignDetail AS a");

            sql.AppendLine("JOIN PB_Material AS b ON a.MaterialId = b.Id");

            sql.AppendLine("LEFT JOIN PB_DesignList AS list ON a.ParentId = list.Id");

            sql.AppendLine("LEFT JOIN PB_Project AS P ON list.ProjectId = P.Id");

            sql.AppendLine("JOIN Base_UnitItems AS c ON b.storageId = c.Id");

            sql.AppendLine("WHERE");

            sql.AppendLine("P.Id = '"+ProjectId+"'");

            sql.AppendLine("AND a.State = 1 UNION ALL");

            sql.AppendLine(" SELECT");

            sql.AppendLine("P.Id ProjectId,");
            sql.AppendLine("a.MaterialId AS Id,");
            sql.AppendLine("b.`Code`,");
            sql.AppendLine("b.`Name`,");
            sql.AppendLine("b.Weight,");
            sql.AppendLine("c.`Name` AS UnitGroupName,");
            sql.AppendLine("(-a.Nums) AS Nums,");
            sql.AppendLine("a.Slot,");
            sql.AppendLine("(-a.RealNums) AS RealNums,");
            sql.AppendLine("(-a.RealNums * b.Weight) AS RealNumsWeight,");
            sql.AppendLine("a.State,");
            sql.AppendLine("a.Id AS SubId,");
            sql.AppendLine("b.IsStandard");
            sql.AppendLine("FROM");
            sql.AppendLine("PB_DesignDetail AS a");
            sql.AppendLine("JOIN PB_Material AS b ON a.MaterialId = b.Id");
            sql.AppendLine("LEFT JOIN PB_DesignList AS list ON a.ParentId = list.Id");
            sql.AppendLine("LEFT JOIN PB_Project AS P ON list.ProjectId = P.Id");
            sql.AppendLine("JOIN Base_UnitItems AS c ON b.storageId = c.Id");
            sql.AppendLine("WHERE");
            sql.AppendLine("P.Id = '"+ProjectId+"'");
            sql.AppendLine("AND a.State = 2");
            sql.AppendLine(") AS t");
            sql.AppendLine("GROUP BY");
            sql.AppendLine(" t.ProjectId");
            return await Db.GetListBySqlAsync<ProjectDeliveryRateGroup>(sql.ToString());
        }
        /// <summary>
        /// 计算已发数量总重 计算发货率
        /// </summary>
        /// <param name="ProjectId">项目id</param>
        /// <returns></returns>
        public async Task<List<DeliveredDeliveryrate>> GetD2(string ProjectId)
        {
            StringBuilder sql = new StringBuilder();
             sql.AppendLine("-- 获取发货物料数量");
            sql.AppendLine("SELECT");
            sql.AppendLine("IFNULL(sum(C.OutNum * M.Weight), 0) GrossWeightDelivered");
            sql.AppendLine("FROM");
            sql.AppendLine("PB_DesignList AS A");
            sql.AppendLine("LEFT JOIN PB_Buildingnumber as bu on A.BuildingNumberID = bu.Id");
            sql.AppendLine("LEFT JOIN PB_DesignDetail AS F ON F.ParentId = A.Id");
            sql.AppendLine("LEFT JOIN PB_PickUpList e ON F.Id = e.SubId AND e.MaterialId = F.MaterialId");
            sql.AppendLine("LEFT JOIN PB_Material AS M ON F.MaterialId = M.Id");
            sql.AppendLine("LEFT JOIN TD_OutStorage AS TD_OutStorage ON e.ParentId = TD_OutStorage.RefCode");

      sql.AppendLine("LEFT JOIN TD_OutStorRecord AS C ON TD_OutStorage.Id = C.OutStorId AND C.MaterialId = F.MaterialId ") ;       
         sql.AppendLine("WHERE");
            sql.AppendLine("A.ProjectId = '"+ProjectId+"'");
            sql.AppendLine("GROUP BY A.ProjectId");
            return await Db.GetListBySqlAsync<DeliveredDeliveryrate>(sql.ToString());
        }
        public async Task<List<ReturnRateGroup>> GetD3(string ProjectId) {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT");
            sql.AppendLine("IFNULL(sum(log.ThisReturnNums * material.Weight), 0) ReturnRate");
            sql.AppendLine("FROM");
            sql.AppendLine("PB_ReturnDetailLog AS log");
            sql.AppendLine("LEFT JOIN PB_Material AS material on log.MaterialId = material.Id");
            sql.AppendLine("WHERE");
            sql.AppendLine("log.ProjectId = '"+ProjectId+"'");
            sql.AppendLine("GROUP BY");
            sql.AppendLine("log.ProjectId");
            return await Db.GetListBySqlAsync<ReturnRateGroup>(sql.ToString());

        }
        /// <summary>
        /// 项目看板
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public async Task<ProjectStatistics> projectStatisticsInfo(string projectId)
        {
            ProjectStatistics projectStatistics = new ProjectStatistics();
            List<BuildingStatesList> buildingStatesList = new List<BuildingStatesList>();
            List<Images> Images = new List<Images>();
            String sql = "SELECT t.`Status` 'States', t.BulidNo 'BuildingNo', t.Bulid 'Id',t.TechnicalDirector 'Name' FROM (SELECT  b.Id Bulid, b.Buildingnumber AS BulidNo,c.`Name` AS ProjectName,u.RealName TechnicalDirector,e.`Name` Typename,b.StartingLayer AS Qishi,b.TerminationLayer AS Zhongzhi,b.StandsNumber AS StandsNumber,b.LiftPlatNums AS LiftPlatNums,  a.ProjectId, a.BuildingnumberId,   a.CreateTime,  a.Remarks, a.`Status`, a.NumberLayers  ThisBuilNo, ROW_NUMBER() over(PARTITION BY a.ProjectId, a.BuildingnumberId ORDER BY a.CreateTime DESC) AS rowsid FROM PB_ProjectStatuLog as a LEFT JOIN PB_Buildingnumber as b ON  a.BuildingnumberId = b.Id LEFT JOIN PB_Project as c ON a.ProjectId = c.Id LEFT JOIN Base_User as u ON b.TechnicalDirector = u.Id LEFT JOIN Base_EnumItem as e on c.Businesstype = e.`Value`) AS t WHERE rowsid = 1 and t.ProjectId = '" + projectId+"'";
            buildingStatesList = await Db.GetListBySqlAsync<BuildingStatesList>(sql);
            if (buildingStatesList.Count>0) {
                foreach (var item in buildingStatesList) {
                    string imgs = " select ImageUrl AS Image  from PB_ProjectStatuLog WHERE BuildingnumberId ='" + item.Id+"' ORDER BY CreateTime DESC LIMIT 1";
                    Images = await Db.GetListBySqlAsync<Images>(imgs);
                    string[] img = Images[0].Image.Split(',');
                    List<string > Strlist= img.ToList();
                    item.Images = Strlist;
                }
            }
            projectStatistics.buildingStatesLists = buildingStatesList;

            #region 项目信息
            projectStatistics.ProjectInfor = Db.GetListBySqlAsync<ProjectInformation>(@"SELECT a.`Name` EntryName,	b.RealName PersonName ,IFNULL(c.`Status`,'') ProjectCycle FROM
	PB_Project a LEFT  JOIN Base_User b ON a.ContactsId = b.Id  LEFT JOIN PB_ProjectStatuLog as  c on c.ProjectId=a.Id  WHERE a.Id = '"+projectId+"' ORDER BY  c.CreateTime DESC LIMIT 1").Result.FirstOrDefault();
            #endregion
            #region 饼图
            PieChart pieChart = new PieChart();
            //设计清单总重
            var DesignGrossWeight = await GetD(projectId);
            //已发数量总重
            var Totalshippingweight = await GetD2(projectId);
            //已回数量总重
            var ReturnRadate = await GetD3(projectId);
            pieChart.DeliveryRate = Totalshippingweight.Count == 0 ? "0":Math.Round((Convert.ToDouble(Totalshippingweight[0].GrossWeightDelivered)/Convert.ToDouble(DesignGrossWeight[0].OtalWeightOfList))*100, 2).ToString();
            pieChart.ReturnRate = ReturnRadate.Count == 0 ? "0" : Math.Round((Convert.ToDouble(ReturnRadate[0].ReturnRate) / Convert.ToDouble(Totalshippingweight[0].GrossWeightDelivered)) * 100, 2).ToString();
            projectStatistics.CargoRate=pieChart;

            #endregion

            #region 图片地址 暂取项目信息图片地址
            projectStatistics.PictureAddress = Db.GetListBySqlAsync<ProjectImgUrl>(@"select  ProjectImg FROM  PB_Project WHERE id='" + projectId + "'").Result.FirstOrDefault();
            #endregion

            #region 项目进度
            ProjectSchedule projectSchedule = new ProjectSchedule();
            projectStatistics.ProjectScheduleinfo = Db.GetListBySqlAsync<ProjectSchedule>(@"

SELECT
  d.SigningTime 'SigningTime',      -- 签订时间
	d.SigningState 'SigningState' , -- 签订状态
	d.SigningPercentage 'SigningPercentage',-- 签订百分比
	
	d.NotificationTime 'NotificationTime', -- 通知时间
 '正常'  'NotificationState',
	
	
	d.PreparationTime 'PreparationTime', -- 备货时间
	d.PreparationState 'PreparationState', -- 备货状态
	 round( d.ReadyNumsWeight	/DesignList.OtalWeightOfList*100,2) AS 'PreparationPercentage',  -- 备货率
	
	
	d.DeliveryTime 'DeliveryTime',                                         -- 发货时间
	d.DeliveryState 'DeliveryState' ,                                      -- 发货状态
		 round(d.OutNumWeight/DesignList.OtalWeightOfList*100,2) AS 'DeliveryPercentage'  , -- 发货百分比
	
 d.ReceivingTime 'ReceivingTime',   -- 收货时间
 d.ReceivingState 'ReceivingState', -- 收货状态
 d.ReceivingPercentage 'ReceivingPercentage', -- 收货百分比


 d.ReturnTime 'ReturnTime' ,-- 回货时间
 d.ReturnState 'ReturnState', -- 回货状态
 round( Returndata.ReturnRate/d.OutNumWeight*100,2) AS 'ReturnPercentage', -- 回货百分比


d.ClosingTime 'ClosingTime', -- 关闭时间
d.ClosingState 'ClosingState' -- 关闭状态

FROM
	(
	SELECT
		a.Id Aid,
		a.`Name`,
		a.CreateTime SigningTime,
		IFNULL( SUM( C.OutNum * M.Weight ), 0 ) OutNumWeight, -- 已发重量
		IFNULL( SUM( e.ReadyNums * M.Weight ), 0 ) ReadyNumsWeight, -- 已经备货重量
		(
		SELECT
			b1.CreateTime 
		FROM
			PB_Project a1
			LEFT JOIN PB_DeliveryNotice b1 ON b1.ProjectId = a1.Id 
		WHERE
			b1.ProjectId = a.Id 
			AND b1.type = 1 
		ORDER BY
			b1.CreateTime DESC 
			LIMIT 1 
		) NotificationTime,
CASE
		
		WHEN IFNULL( a.CreateTime, 0 )= 0 THEN
		'未签订' ELSE '正常' 
	END AS SigningState,
CASE
		
		WHEN IFNULL( a.CreateTime, 0 )= 0 THEN
		0 ELSE 100 
	END AS SigningPercentage,

	CASE
			
			WHEN IFNULL( sum( e.ReadyNums ), 0 )= 0 THEN
			'未备货' ELSE '正常' 
		END AS PreparationState,
		(
		SELECT
			d1.CreateTime 
		FROM
			PB_Project a1
			LEFT JOIN PB_DeliveryNotice b1 ON a1.Id = b1.ProjectId
			LEFT JOIN PB_DeliveryNoticeItem c1 ON c1.ParentId = b1.Id
			LEFT JOIN PB_PickUpMain d1 ON d1.ParentId = c1.Id
			LEFT JOIN PB_PickUpList e1 ON d1.Id = e1.ParentId 
		WHERE
			a1.`Name` = a.`Name` 
		ORDER BY
			d1.CreateTime DESC 
			LIMIT 1 
		) PreparationTime,-- 发货时间
	
	CASE
			
			WHEN IFNULL( sum( e.ReadyNums ), 0 )= 0 THEN
			'未发货' ELSE '正常' 
		END AS DeliveryState,
		(
		SELECT
			bill.DepartureTime 
		FROM
			PB_Project a1
			LEFT JOIN PB_DeliveryNotice AS b1 ON b1.ProjectId = a1.Id
			INNER JOIN PB_TransportBill AS bill ON bill.DeliveryNoticeId = b1.Id 
		WHERE
			b1.ProjectId = a.Id 
			AND b1.type = 1 
		ORDER BY
			bill.DepartureTime DESC 
			LIMIT 1 
		) DeliveryTime,-- 收货时间
	CASE
			
			WHEN IFNULL( sum( e.ReadyNums ), 0 )= 0 THEN
			'未收货' ELSE '正常' 
		END AS ReceivingState,
		(
		SELECT
			bill.AcceptanceTime 
		FROM
			PB_Project a1
			LEFT JOIN PB_DeliveryNotice AS b1 ON b1.ProjectId = a1.Id
			INNER JOIN PB_TransportBill AS bill ON bill.DeliveryNoticeId = b1.Id 
			LEFT JOIN PB_TransportBillDetail AS Detail ON Detail.ParentId=bill.Id
		WHERE
			b1.ProjectId = a.Id 
			AND b1.type = 1 
		ORDER BY
			bill.AcceptanceTime DESC 
			LIMIT 1 
		) ReceivingTime,-- 回货时间
	CASE
			
			WHEN IFNULL( sum( e.ReadyNums ), 0 )= 0 THEN
			'未回货' ELSE '正常' 
		END AS ReturnState,
		(
		SELECT
			b1.CreateTime 
		FROM
			PB_Project a1
			LEFT JOIN PB_Return AS b1 ON b1.ParentId = a1.Id 
		WHERE
			b1.ParentId = a.Id 
		ORDER BY
			b1.CreateTime DESC 
			LIMIT 1 
		) ReturnTime,
		0 ReceivingPercentage,
		0 ReturnPercentage,
		IFNULL( a.ClosingDate, '' ) AS ClosingTime,
		'正常' ClosingState,
		0 ClosingPercentage 
	FROM
		PB_Project AS a
		LEFT JOIN PB_DesignList AS list ON list.ProjectId = a.Id
		LEFT JOIN PB_Buildingnumber AS bu ON list.BuildingNumberID = bu.Id
		LEFT JOIN PB_DesignDetail AS Detail ON Detail.ParentId = list.Id -- 获取发货物料数量
		LEFT JOIN PB_PickUpList e ON Detail.Id = e.SubId 
		AND e.MaterialId = Detail.MaterialId
		LEFT JOIN PB_Material AS M ON Detail.MaterialId = M.Id --  获取已备数据物料数量
		LEFT JOIN TD_OutStorage AS TD_OutStorage ON e.ParentId = TD_OutStorage.RefCode
		LEFT JOIN TD_OutStorRecord AS C ON TD_OutStorage.Id = C.OutStorId 
		AND C.MaterialId = Detail.MaterialId WHERE a.Id = '" + projectId+"' GROUP BY a.`Name`,a.Id ) AS d LEFT JOIN( SELECT  SUM(RealNums) AS RealNums, SUM( RealNumsWeight ) AS OtalWeightOfList,	 ProjectId FROM  ( SELECT   P.Id ProjectId, a.MaterialId AS Id,   b.`Code`,  b.`Name`, b.Weight,  c.`Name` AS UnitGroupName,  a.Nums,   a.Slot,   a.RealNums, a.RealNums * b.Weight AS RealNumsWeight,  a.State,  a.Id AS SubId,  b.IsStandard FROM  PB_DesignDetail AS a  JOIN PB_Material AS b ON a.MaterialId = b.Id  LEFT JOIN PB_DesignList AS list ON a.ParentId = list.Id LEFT JOIN PB_Project AS P ON list.ProjectId = P.Id JOIN Base_UnitItems AS c ON b.storageId = c.Id  WHERE   P.Id = '"+projectId+"' AND a.State = 0 UNION ALL   SELECT   P.Id ProjectId,   a.MaterialId AS Id,   b.`Code`,    b.`Name`,   b.Weight,   c.`Name` AS UnitGroupName,   a.Nums,  a.Slot,  a.RealNums,   a.RealNums * b.Weight AS RealNumsWeight,   a.State,  a.Id AS SubId,  b.IsStandard   FROM  PB_DesignDetail AS a   JOIN PB_Material AS b ON a.MaterialId = b.Id  LEFT JOIN PB_DesignList AS list ON a.ParentId = list.Id   LEFT JOIN PB_Project AS P ON list.ProjectId = P.Id JOIN Base_UnitItems AS c ON b.storageId = c.Id   WHERE  P.Id = '"+projectId+"'  AND a.State = 1 UNION ALL  SELECT   P.Id ProjectId,  a.MaterialId AS Id,   b.`Code`,  b.`Name`,  b.Weight,  c.`Name` AS UnitGroupName,   (-a.Nums) AS Nums,   a.Slot,  (-a.RealNums) AS RealNums, (-a.RealNums * b.Weight) AS RealNumsWeight,   a.State,   a.Id AS SubId,   b.IsStandard   FROM   PB_DesignDetail AS a   JOIN PB_Material AS b ON a.MaterialId = b.Id  LEFT JOIN PB_DesignList AS list ON a.ParentId = list.Id LEFT JOIN PB_Project AS P ON list.ProjectId = P.Id   JOIN Base_UnitItems AS c ON b.storageId = c.Id  WHERE  P.Id = '"+projectId+"'  AND a.State = 2  ) AS t GROUP BY   t.ProjectId) AS  DesignList ON  DesignList.ProjectId=d.Aid LEFT JOIN(  SELECT IFNULL(sum(log.ThisReturnNums * material.Weight), 0) ReturnRate,	log.ProjectId   FROM    PB_ReturnDetailLog AS log  LEFT JOIN PB_Material AS material on log.MaterialId = material.Id  WHERE  log.ProjectId = '"+projectId+"'  GROUP BY  log.ProjectId) AS Returndata ON   Returndata.ProjectId =d.Aid  ").Result.FirstOrDefault();
      
            #endregion

            #region 统计信息

//            string sqlMain = @"SELECT
//	T.*,
//	IFNULL( LOGSS.SUM, 0 ) ReturnNums ,
//	IFNULL( ReceiptWarehousing.ReceiptQuantity,0)   'ReceiptQuantity'
//FROM
//	(
//	SELECT
//		M.`Code` number,
//		M.`Name` ProductName,
//		M.ID,
//		F.RealNums AS DesignQuantity,
//		IFNULL( SUM( e.ReadyNums ), 0 ) PreparedQuantity,
//		IFNULL( SUM( C.OutNum ), 0 ) IssuedQuantity,
//		bu.Buildingnumber AS Buildingnumber,
//		F.MaterialId MaterialId,
//		A.BuildingNumberID 
//	FROM
//		PB_DesignList AS A
//		LEFT JOIN PB_Buildingnumber AS bu ON A.BuildingNumberID = bu.Id
//		LEFT JOIN PB_DesignDetail AS F ON F.ParentId = A.Id -- 获取发货物料数量
//		LEFT JOIN PB_PickUpList e ON F.Id = e.SubId 
//		AND e.MaterialId = F.MaterialId
//		LEFT JOIN PB_Material AS M ON F.MaterialId = M.Id --  获取已备数据物料数量
//		LEFT JOIN TD_OutStorage AS TD_OutStorage ON e.ParentId = TD_OutStorage.RefCode
//		LEFT JOIN TD_OutStorRecord AS C ON TD_OutStorage.Id = C.OutStorId 
//		AND C.MaterialId = F.MaterialId 
//	WHERE
//		A.ProjectId = '" + projectId+ "' GROUP BY A.Id,F.MaterialId,F.RealNums,e.SubId ORDER BY M.`Code` ) AS T LEFT JOIN (SELECT IFNULL( Sum( returnss.ThisReturnNums ), 0 ) AS SUM, returnss.BuildingId, returnss.MaterialId FROM PB_Return AS log LEFT JOIN PB_ReturnDetailLog AS returnss ON returnss.ParentId = log.Id  GROUP BY returnss.BuildingId, returnss.MaterialId ) AS LOGSS ON LOGSS.BuildingId = T.BuildingNumberID AND LOGSS.MaterialId = T.MaterialId LEFT JOIN(  select IFNULL(SUM(Detailtable.Num),0) ReceiptQuantity,Returntable.BuildingId,Detailtable.MaterialId from TD_InStorDetail AS Detailtable LEFT JOIN TD_InStorage AS   Maintable on Detailtable.InStorId=Maintable.Id LEFT JOIN TD_Receiving AS   Receivingtable on  Maintable.RecId=Receivingtable.Id LEFT  JOIN PB_Return  AS  Returntable on  Receivingtable.OrderId=Returntable.Id GROUP BY Returntable.BuildingId,Detailtable.MaterialId ) AS ReceiptWarehousing ON ReceiptWarehousing.BuildingId=T.BuildingNumberID AND ReceiptWarehousing.MaterialId=T.MaterialId";

            //获取统计数据
           // StringBuilder StatisticsDatas = new StringBuilder();
           // StatisticsDatas.AppendLine(" -- 项目看板 sql 调整");
           // StatisticsDatas.AppendLine("-- 项目看板 表格调");
           // StatisticsDatas.AppendLine("SELECT -- 设计清单信息");
           // StatisticsDatas.AppendLine("OK.BuildingNumber,");
           // StatisticsDatas.AppendLine("OK.RealNums 'DesignQuantity',");
           // StatisticsDatas.AppendLine("OK.`MaterialCode` 'number',");
           // StatisticsDatas.AppendLine("OK.MaterialName 'ProductName',");
           // StatisticsDatas.AppendLine("OK.ParentId,");
           // StatisticsDatas.AppendLine("OK.MaterialId,");
           // StatisticsDatas.AppendLine("OK.buildingID, -- 备货单数据");
           // StatisticsDatas.AppendLine("IFNULL(PB_PickUpList.PreparedQuantity, 0) PreparedQuantity, -- 发货数据");
           // StatisticsDatas.AppendLine("IFNULL(PB_PickUpList.IssuedQuantity, 0) IssuedQuantity, -- 回货数据");
           // StatisticsDatas.AppendLine("IFNULL(ReturnData.ReturnNums, 0) ReturnNums,-- 获取入库数量");
           // StatisticsDatas.AppendLine("IFNULL(ReceiptWarehousing.ReceiptQuantity, 0) ReceiptQuantity");
           // StatisticsDatas.AppendLine("FROM");
           // StatisticsDatas.AppendLine(" (");
           // StatisticsDatas.AppendLine("SELECT");
           // StatisticsDatas.AppendLine(" SUM(Nums) AS 'Nums',");
           // StatisticsDatas.AppendLine("  SUM(RealNums) AS 'RealNums',");
           // StatisticsDatas.AppendLine(" ParentId AS 'ParentId',");
           // StatisticsDatas.AppendLine("   MaterialId AS 'MaterialId',");
           // StatisticsDatas.AppendLine("  MaterialName AS 'MaterialName',");
           // StatisticsDatas.AppendLine("MaterialCode AS 'MaterialCode',");
           // StatisticsDatas.AppendLine(" BuildingNumber AS 'BuildingNumber',");
           // StatisticsDatas.AppendLine(" buildingID AS 'buildingID'");
           // StatisticsDatas.AppendLine(" FROM");
           // StatisticsDatas.AppendLine(" (");
           // StatisticsDatas.AppendLine(" SELECT");
           // StatisticsDatas.AppendLine("building.Buildingnumber BuildingNumber,");
           // StatisticsDatas.AppendLine(" building.Id buildingID,");
           // StatisticsDatas.AppendLine("M.`Name` MaterialName,");
           // StatisticsDatas.AppendLine("M.`Code` MaterialCode, ");
           // StatisticsDatas.AppendLine("a.ParentId,");
           // StatisticsDatas.AppendLine("a.MaterialId AS MaterialId,");
           // StatisticsDatas.AppendLine("a.Nums,");
           // StatisticsDatas.AppendLine("a.Slot,");
           // StatisticsDatas.AppendLine("a.RealNums,");
           // StatisticsDatas.AppendLine("a.State,");
           // StatisticsDatas.AppendLine("a.Id AS SubId");
           // StatisticsDatas.AppendLine("FROM");
           // StatisticsDatas.AppendLine("PB_DesignDetail AS a");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_Buildingnumber AS building ON List.BuildingNumberID = building.Id");
           // StatisticsDatas.AppendLine("WHERE");
           // StatisticsDatas.AppendLine("a.State = 0");
           // StatisticsDatas.AppendLine("AND List.ProjectId = '"+projectId+"' UNION ALL");
           // StatisticsDatas.AppendLine("SELECT");
           // StatisticsDatas.AppendLine("building.Buildingnumber AS 'BuildingNumber',");
           // StatisticsDatas.AppendLine("building.Id buildingID,");
           // StatisticsDatas.AppendLine("M.`Name` MaterialName,");
           // StatisticsDatas.AppendLine("M.`Code` MaterialCode,");
           // StatisticsDatas.AppendLine("a.ParentId,");
           // StatisticsDatas.AppendLine("a.MaterialId AS MaterialId,");
           // StatisticsDatas.AppendLine("a.Nums,");
           // StatisticsDatas.AppendLine("a.Slot,");
           // StatisticsDatas.AppendLine("a.RealNums,");
           // StatisticsDatas.AppendLine("a.State,");
           // StatisticsDatas.AppendLine("a.Id AS SubId");
           // StatisticsDatas.AppendLine("FROM");
           // StatisticsDatas.AppendLine("PB_DesignDetail AS a");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_Buildingnumber AS building ON List.BuildingNumberID = building.Id");
           // StatisticsDatas.AppendLine("WHERE");
           // StatisticsDatas.AppendLine("State = 1");
           //StatisticsDatas.AppendLine("AND List.ProjectId = '"+projectId+"' UNION ALL");
           // StatisticsDatas.AppendLine("SELECT");
           // StatisticsDatas.AppendLine("building.Buildingnumber AS 'BuildingNumber',");
           // StatisticsDatas.AppendLine("building.Id buildingID,");
           // StatisticsDatas.AppendLine("M.`Name` MaterialName,");
           // StatisticsDatas.AppendLine("M.`Code` MaterialCode,");
           // StatisticsDatas.AppendLine("a.ParentId,");
           //StatisticsDatas.AppendLine(" a.MaterialId AS MaterialId,");
           // StatisticsDatas.AppendLine("(-a.Nums) AS Nums,");
           // StatisticsDatas.AppendLine(" a.Slot,");
           // StatisticsDatas.AppendLine("(-a.RealNums) AS RealNums,");
           // StatisticsDatas.AppendLine("a.State,");
           // StatisticsDatas.AppendLine("a.Id AS SubId");
           // StatisticsDatas.AppendLine("FROM");
           // StatisticsDatas.AppendLine("PB_DesignDetail AS a");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_Buildingnumber AS building ON List.BuildingNumberID = building.Id");
           // StatisticsDatas.AppendLine("WHERE");
           // StatisticsDatas.AppendLine("a.State = 2");
           // StatisticsDatas.AppendLine("AND List.ProjectId = '"+projectId+"'");
           // StatisticsDatas.AppendLine(") AS k");
           // StatisticsDatas.AppendLine("GROUP BY");
           // StatisticsDatas.AppendLine("MaterialId,");
           // StatisticsDatas.AppendLine("ParentId,");
           // StatisticsDatas.AppendLine("MaterialName,");
           // StatisticsDatas.AppendLine("BuildingNumber,");
           // StatisticsDatas.AppendLine("MaterialCode,");
           // StatisticsDatas.AppendLine("buildingID");
           // StatisticsDatas.AppendLine("ORDER BY");
           // StatisticsDatas.AppendLine("BuildingNumber,");
           // StatisticsDatas.AppendLine("MaterialCode");
           // StatisticsDatas.AppendLine(" ) AS OK  -- 获取备货单数量");
           // StatisticsDatas.AppendLine("LEFT JOIN(");
           // StatisticsDatas.AppendLine("SELECT");
           // StatisticsDatas.AppendLine("a.MaterialId AS MaterialId,");
           // StatisticsDatas.AppendLine("IFNULL(sum(a.ReadyNums ), 0 ) PreparedQuantity,");
           // StatisticsDatas.AppendLine("IFNULL(sum(C.OutNum), 0) IssuedQuantity,");
           // StatisticsDatas.AppendLine("List.ParentId");
           // StatisticsDatas.AppendLine("FROM");
           // StatisticsDatas.AppendLine("PB_PickUpList AS a");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_DesignDetail AS List ON a.SubId = List.Id");
           // StatisticsDatas.AppendLine("LEFT JOIN TD_OutStorage AS TD_OutStorage ON a.ParentId = TD_OutStorage.RefCode");
           // StatisticsDatas.AppendLine("LEFT JOIN TD_OutStorRecord AS C ON TD_OutStorage.Id = C.OutStorId");
           // StatisticsDatas.AppendLine("AND C.MaterialId = List.MaterialId");
           // StatisticsDatas.AppendLine("GROUP BY");
           // StatisticsDatas.AppendLine("a.MaterialId,");
           // StatisticsDatas.AppendLine("ParentId");
           // StatisticsDatas.AppendLine(") AS PB_PickUpList ON PB_PickUpList.ParentId = OK.ParentId");
           // StatisticsDatas.AppendLine("AND PB_PickUpList.MaterialId = OK.MaterialId");
           // StatisticsDatas.AppendLine("LEFT JOIN(");
           // StatisticsDatas.AppendLine("SELECT");
           // StatisticsDatas.AppendLine("IFNULL(Sum(returnss.ThisReturnNums ), 0 ) AS ReturnNums,");
           // StatisticsDatas.AppendLine("returnss.BuildingId,");
           // StatisticsDatas.AppendLine("returnss.MaterialId");
           // StatisticsDatas.AppendLine("FROM");
           // StatisticsDatas.AppendLine(" PB_Return AS log");
           // StatisticsDatas.AppendLine(" LEFT JOIN PB_ReturnDetailLog AS returnss ON returnss.ParentId = log.Id");
           // StatisticsDatas.AppendLine("GROUP BY");
           // StatisticsDatas.AppendLine("returnss.BuildingId,");
           // StatisticsDatas.AppendLine("returnss.MaterialId");
           // StatisticsDatas.AppendLine(") AS ReturnData ON ReturnData.BuildingId = OK.buildingID");
           // StatisticsDatas.AppendLine("AND ReturnData.MaterialId = OK.MaterialId-- 获取入库数量");
           // StatisticsDatas.AppendLine("LEFT JOIN(");
           // StatisticsDatas.AppendLine("SELECT");
           // StatisticsDatas.AppendLine("IFNULL(SUM(Detailtable.Num), 0) ReceiptQuantity,");
           // StatisticsDatas.AppendLine("Returntable.BuildingId,");
           // StatisticsDatas.AppendLine("Detailtable.MaterialId");
           // StatisticsDatas.AppendLine("FROM");
           // StatisticsDatas.AppendLine("TD_InStorDetail AS Detailtable");
           // StatisticsDatas.AppendLine("LEFT JOIN TD_InStorage AS Maintable ON Detailtable.InStorId = Maintable.Id");
           // StatisticsDatas.AppendLine("LEFT JOIN TD_Receiving AS Receivingtable ON Maintable.RecId = Receivingtable.Id");
           // StatisticsDatas.AppendLine("LEFT JOIN PB_Return AS Returntable ON Receivingtable.OrderId = Returntable.Id");
           // StatisticsDatas.AppendLine("GROUP BY");
           // StatisticsDatas.AppendLine("Returntable.BuildingId,");
           // StatisticsDatas.AppendLine("Detailtable.MaterialId");
           // StatisticsDatas.AppendLine(") AS ReceiptWarehousing ON ReceiptWarehousing.BuildingId = OK.buildingID");
           // StatisticsDatas.AppendLine("AND ReceiptWarehousing.MaterialId = OK.MaterialId");
           // projectStatistics.DataInfo = await Db.GetListBySqlAsync<StatisticalData>(StatisticsDatas.ToString());
    
          return projectStatistics;
            #endregion




        }

        /// <summary>
        /// 获取当前项目负责人管制的项目
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<PB_ProjectBulidList>> GetProjectCurrentState()
        {


            List<PB_ProjectBulidList> DDD = new List<PB_ProjectBulidList>();

            var ContactsId = _operator.UserId;//当前操作者UserId
            List<PB_Buildingnumber> Bumber = await Db.GetIQueryable<PB_Buildingnumber>().Where(m => m.TechnicalDirector == ContactsId).ToListAsync();//获取当前操作者的楼号
            List<PB_Project> pB_Projects = new List<PB_Project>();
            foreach (var ITEM in Bumber)
            {
                PB_Project pB_Project = await Db.GetIQueryable<PB_Project>().Where(m => m.Id == ITEM.ProjectId).FirstOrDefaultAsync();
                pB_Projects.Add(pB_Project);
            }
            //分组过滤项目id
            var List = pB_Projects.GroupBy(x => new { x.Id, x.Name, x.Code, x.State }).Select(x => new { Id = x.Key.Id, Name = x.Key.Name, Code = x.Key.Code, State = x.Key.State, }).ToList();
            foreach (var Project in List)
            {
                PB_ProjectBulidList ProjectList = new PB_ProjectBulidList();
                ProjectList.Id = Project.Id;
                ProjectList.Name = Project.Name;
                ProjectList.Code = Project.Code;
                ProjectList.State = Project.State;
                ProjectList.BuildingnumberList = await Db.GetIQueryable<PB_Buildingnumber>().Where(M => M.ProjectId == Project.Id && M.TechnicalDirector == ContactsId).ToListAsync();
                foreach (var sub in ProjectList.BuildingnumberList)
                {
                    var ss = await Db.GetIQueryable<PB_ProjectStatuLog>().OrderByDescending(x => x.CreateTime).Where(m => m.BuildingnumberId == sub.Id).FirstOrDefaultAsync();
                    if (ss == null)
                    {
                        sub.Memo = "";
                        sub.TechnicalDirector = "";
                    }
                    else if (ss != null)
                    {
                        sub.Memo = ss.Remarks;
                        sub.TechnicalDirector = ss.NumberLayers;
                        sub.CreatorId = ss.Status;
                    }
                }
                DDD.Add(ProjectList);
            }


            return DDD;
        }

        public async Task<string> GetLocation(string lng, string lat)
        {

            HttpClient client = new HttpClient();
            string url = string.Format("http://api.map.baidu.com/geocoder/v2/?ak=你的AK&callback=renderReverse&location={0},{1}&output=json&pois=1Z", lng, lat);
            string result = client.GetStringAsync(url).Result;

            var locationResult = (JObject)JsonConvert.DeserializeObject(result.Replace("renderReverse&&renderReverse", "").Replace("(", "").Replace(")", ""));

            if (locationResult == null || locationResult["result"] == null || locationResult["result"]["formatted_address"] == null)
                return string.Empty;

            var address = Convert.ToString(locationResult["result"]["formatted_address"]);
            if (locationResult["result"]["sematic_description"] != null)
                address += " " + Convert.ToString(locationResult["result"]["sematic_description"]);
            return address;
        }
        /// <summary>
        /// 获取楼号下的项目状态日志
        /// </summary>
        /// <param name="input"></param>
        /// <param name="ProjectId"></param>
        /// <param name="BuildingnumberId"></param>
        /// <returns></returns>
        public async Task<PageResult<PB_ProjectStatuLogDto_>> GetProjectStatuLogList(PageInput<ProjectReturnConditionDTO> input, string ProjectId, string BuildingnumberId)
        {
            var search = input.Search;
            Expression<Func<PB_ProjectStatuLog, PB_Project, PB_Buildingnumber, PB_ProjectStatuLogDto_>> select = (a, b, c) => new PB_ProjectStatuLogDto_
            {
                ProjectName = b.Name,
                BulidNoi = c.Buildingnumber,

            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_ProjectStatuLog>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Project>() on a.ProjectId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Buildingnumber>() on a.BuildingnumberId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    select @select.Invoke(a, b, c);
            if (BuildingnumberId.IsNullOrEmpty())
            {
                q = q.Where(m => m.ProjectId == ProjectId);
            }
            else
            {
                q = q.Where(m => m.ProjectId == ProjectId && m.BuildingnumberId == BuildingnumberId);
            }

            q = q.OrderByDescending(p => p.CreateTime);

            return await q.GetPageResultAsync(input);
        }

        public async Task<List<PB_Buildingnumber>> GetBulidList(string ProjectId)
        {
            var ListData = await Db.GetIQueryable<PB_Buildingnumber>().Where(m => m.ProjectId == ProjectId).ToListAsync();
            return ListData;

        }
        /// <summary>
        /// 大屏显示在用机位总数
        /// </summary>
        /// <returns>机位总数</returns>
        public ScreenDisplayValueDTO GetStandsNumberInUsed()
        {
            string strSQL = @"SELECT IFNULL(SUM(StandsNumber),0) AS value FROM PB_Buildingnumber bn
-- 未完结项目，已巡检楼
 JOIN PB_Project p ON bn.ProjectId=p.Id AND p.State=0 AND bn.IsInspection=1";
            var res = Db.GetListBySqlAsync<ScreenDisplayValueDTO>(strSQL).Result.FirstOrDefault();
            if (res != null)
            {
                //四舍五入保留两位小数
                res.value = Convert.ToDouble(res.value.ToString("#0.##"));
            }
            return res;
        }
        /// <summary>
        /// 大屏显示已签合同未发货的机位数
        /// </summary>
        /// <returns>已签合同未发货的机位数</returns>
        public ScreenDisplayValueDTO GetReadyDeliveryStandsNumber()
        {
            //已签合同已发货的机位数
            string strSQL = @"SELECT IFNULL(SUM(StandsNumber),0) AS value FROM PB_Buildingnumber bn
JOIN PB_Project p ON bn.ProjectId=p.Id AND p.Signingunit IS NOT NULL
JOIN 
(SELECT DISTINCT dn.ProjectId FROM PB_DeliveryNotice dn
JOIN  PB_TransportBillGroup ng ON dn.Id = ng.DeliveryNoticeId
JOIN PB_TransportBill tb ON tb.Id = ng.ParentId AND tb.BillType = 'Delivery' AND TO_DAYS(tb.DepartureTime)-TO_DAYS(NOW())<=0
) a ON a.ProjectId=p.Id";
            //已签合同机位数
            double done = Db.GetListBySqlAsync<ScreenDisplayValueDTO>(strSQL).Result.FirstOrDefault().value;
            strSQL = @"SELECT IFNULL(SUM(StandsNumber),0) AS value FROM PB_Buildingnumber bn
JOIN PB_Project p ON bn.ProjectId=p.Id AND p.Signingunit IS NOT NULL";
            double total = Db.GetListBySqlAsync<ScreenDisplayValueDTO>(strSQL).Result.FirstOrDefault().value;
            ScreenDisplayValueDTO res = new ScreenDisplayValueDTO { value = total - done };
            //四舍五入保留两位小数
            res.value = Convert.ToDouble(res.value.ToString("#0.##"));
            return res;
        }
        public ScreenDisplayValue2DTO GetProjectsunder()
        {
            string sqlProjectsunder = "SELECT count(*) AS value FROM PB_Project WHERE State = 0";
            var sqlProjectsunderList = Db.GetListBySqlAsync<ScreenDisplayValue2DTO>(sqlProjectsunder).Result.FirstOrDefault();
            if (sqlProjectsunderList != null)
            {
                return sqlProjectsunderList;
            }
            else
            {
                throw new BusException("暂无数据");
            }
        }

        /// <summary>
        /// 大屏显示未发项目数量总数
        /// </summary>
        /// <returns></returns>
        public ScreenDisplayValue2DTO GetTotalNumber()
        {
            //不纳入发货计划的发货项目也统计进来 牟，2022.5.24
            //string sqlnums = "select count(DISTINCT dd.ProjectId) as value from  PB_DeliveryPlanDetails as dd  JOIN PB_DeliveryPlan dp ON dp.States IN(1,2)  AND dd.ParentId = dp.Id JOIN  PB_DesignList dl ON dd.ProjectId = dl.ProjectId AND dd.BuildingId = dl.BuildingNumberID JOIN  PB_DeliveryNoticeItem dni ON dl.Id = dni.DesignId JOIN  PB_DeliveryNotice dn ON dn.Id = dni.ParentId JOIN  PB_TransportBillGroup ng ON dn.Id = ng.DeliveryNoticeId JOIN PB_TransportBill tb ON tb.Id = ng.ParentId AND tb.BillType = 'Delivery' AND TO_DAYS(tb.DepartureTime)-TO_DAYS(NOW())<=0";
            string sqlnums = @"select count(DISTINCT dn.ProjectId) as value 
from  PB_DeliveryNotice dn  
JOIN  PB_TransportBillGroup ng ON dn.Id = ng.DeliveryNoticeId 
JOIN PB_TransportBill tb ON tb.Id = ng.ParentId AND tb.BillType = 'Delivery' AND TO_DAYS(tb.DepartureTime)-TO_DAYS(NOW())<=0";
            int num = Db.GetListBySqlAsync<ScreenDisplayValue2DTO>(sqlnums).Result.FirstOrDefault().value;
            //所有未完结的项目数 牟，2022.5.24
            //string sqlnum = "SELECT COUNT(DISTINCT dd.ProjectId) AS value FROM PB_DeliveryPlanDetails dd JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND dd.ParentId = dp.Id";
            string sqlnum = @"SELECT COUNT(1) AS value FROM PB_Project WHERE State=0";
            int nums = Db.GetListBySqlAsync<ScreenDisplayValue2DTO>(sqlnum).Result.FirstOrDefault().value;
            ScreenDisplayValue2DTO totalNumber = new ScreenDisplayValue2DTO() { value = (nums - num) };
            return totalNumber;
        }
        /// <summary>
        /// 大屏显示交付监控
        /// </summary>
        /// <returns>交付监控</returns>
        public async Task<List<PB_ProjectDeliveryMonitorDTO>> GetPB_ProjectDeliveryMonitor()
        {
            //实发货数量大于0时为交付              等于发货清单应发数量为交付
            string strSQL = @"SELECT a.ProjectName,IFNULL(a.StandsNums,0) AS StandsNumber,b.AcceptanceTime AS SubmitTime,a.RealNums,b.PackNums 
FROM
(SELECT dl.ProjectId,p.`Name` AS ProjectName,IFNULL(SUM(CAST(SUBSTRING_INDEX(dl.StandsNums,'（',1) AS UNSIGNED)),0) AS StandsNums,IFNULL(SUM(dd.RealNums),0) AS RealNums
FROM PB_DesignDetail dd
JOIN PB_DesignList dl ON dl.ExamineStatus=2 AND dd.ParentId = dl.Id
JOIN PB_Project p ON p.Id=dl.ProjectId
GROUP BY dl.ProjectId) a
LEFT JOIN
(SELECT dl.ProjectId,p.`Name` AS ProjectName,IFNULL(SUM(td.PackNums),0) AS PackNums,MAX(tb.AcceptanceTime) AS AcceptanceTime
    FROM PB_DesignDetail dd
    JOIN PB_DesignList dl ON dl.ExamineStatus=2 AND dd.ParentId = dl.Id
    JOIN PB_Project p ON p.Id=dl.ProjectId
    JOIN PB_TransportBillDetail td ON td.DesignDetailId=dd.Id
    JOIN PB_TransportBill tb ON td.ParentId=tb.Id AND tb.BillType = 'Delivery' AND TO_DAYS(tb.DepartureTime)-TO_DAYS(NOW())<=0
GROUP BY dl.ProjectId) b
ON a.ProjectId=b.ProjectId AND b.PackNums>0";  // =a.RealNums";
            var res = await Db.GetListBySqlAsync<PB_ProjectDeliveryMonitorDTO>(strSQL);
            return res;
        }
        /// <summary>
        /// 大屏显示爬架交付状态
        /// </summary>
        /// <returns>爬架交付状态</returns>
        public async Task<List<PB_ProjectSubmitStateDTO>> GetProjectSubmitState()
        {
            string strSQL = @"SELECT p.`Name` AS ProjectName,IFNULL(MIN(pm.`Status`),0) AS PickUpStatus,IFNULL(MIN(tb.State),0) AS TransportState,MAX(tb.DepartureTime) AS DepartureTime,'已通知' AS StateName
FROM  PB_Project p
JOIN  PB_DeliveryNotice dn ON dn.ProjectId=p.Id AND dn.AuditStatus=1 AND dn.Type=1
JOIN  PB_DeliveryNoticeItem dni ON dni.ParentId=dn.Id
LEFT JOIN  PB_PickUpMain pm ON pm.ParentId=dni.Id AND pm.`Status`=3
LEFT JOIN  PB_TransportBillGroup ng ON dn.Id = ng.DeliveryNoticeId
LEFT JOIN PB_TransportBill tb ON tb.Id = ng.ParentId AND tb.BillType = 'Delivery'
GROUP BY p.Id
ORDER BY PickUpStatus ASC,TransportState ASC";
            var res = await Db.GetListBySqlAsync<PB_ProjectSubmitStateDTO>(strSQL);
            if (res.Count > 0)
            {
                res.ForEach(aData =>
                {
                    if (aData.DepartureTime == null || aData.TransportState == null)
                    {
                        if (aData.PickUpStatus >= 1)
                        {
                            aData.StateName = "备货中";
                        }
                    }
                    else
                    {
                        if (aData.DepartureTime != null)
                        {
                            aData.StateName = "已发货";
                        }
                        if (aData.TransportState == 6)
                        {
                            aData.StateName = "已收货";
                        }
                    }
                });

            }
            return res;
        }
        /// <summary>
        /// 大屏显示回货项目数量
        /// </summary>
        /// <returns>回货项目数量</returns>
        public ScreenDisplayValue2DTO GetReturnProjectNumber()
        {
            string strSQL = @"SELECT COUNT(DISTINCT ParentId) AS value FROM PB_Return WHERE State>0";
            var res = Db.GetListBySqlAsync<ScreenDisplayValue2DTO>(strSQL).Result.FirstOrDefault();
            return res;
        }
        /// <summary>
        /// 大屏显示物料回货百分比均值(先计算单品回货率，再求平均数)
        /// </summary>
        /// <returns>物料回货百分比均值</returns>
        public ScreenDisplayValue3DTO GetReturnAverage()
        {
            string strSQL = @"SELECT SUM(t.value),COUNT(t.MaterialId),SUM(t.value)/COUNT(t.MaterialId) AS value
FROM
(SELECT a.MaterialId,IF(a.PackNums=0,0,IF(a.PackNums<b.ReturnNums,100,IFNULL(b.ReturnNums/a.PackNums,0)*100))  AS value
FROM
(SELECT td.MaterialId,IFNULL(SUM(td.PackNums),0) AS PackNums
FROM PB_TransportBillDetail td
JOIN PB_TransportBill tb ON td.ParentId=tb.Id AND tb.BillType='Delivery' AND TO_DAYS(tb.DepartureTime)-TO_DAYS(NOW())<=0
GROUP BY td.MaterialId) a
LEFT JOIN
(SELECT MaterialId,IFNULL(SUM(ReturnNums),0) AS ReturnNums
FROM PB_ReturnDetailLog
GROUP BY MaterialId) b ON a.MaterialId=b.MaterialId) t";
            var res = Db.GetListBySqlAsync<ScreenDisplayValueDTO>(strSQL).Result.FirstOrDefault();
            res.value = Convert.ToDouble(res.value.ToString("#0.##"));
            return new ScreenDisplayValue3DTO { value = res.value + "%" };
        }
        /// <summary>
        /// 大屏显示30天项目回货率(先计算单品回货率，再求平均数)，损坏率列表。
        /// </summary>
        /// <returns>30天项目回货率，损坏率</returns>
        public async Task<List<PB_ProjectReturnDestoriedRateDTO>> Get30DayReturnDestoriedRate()
        {
            //项目分组，项目内再算物料的平均回货、损坏率
            //            string strSQL = @"SELECT t.Name AS ProjectName,
            //IF(COUNT(t.MaterialId)=0,0,IFNULL(SUM(data),0)/COUNT(t.MaterialId))*100 AS ReturnRate,
            //IF(COUNT(t.MaterialId)=0,0,IFNULL(SUM(DestoriedRate),0)/COUNT(t.MaterialId))*100 AS DestoriedRate
            //FROM 
            //(SELECT a.ProjectId,a.Name,a.MaterialId,
            //IF(a.PackNums IS NULL,0,IF(b.ReturnNums>a.PackNums,1,IFNULL(b.ReturnNums,0)/a.PackNums)) AS data,
            //IF(b.ReturnNums IS NULL OR c.SortingSum>b.ReturnNums,0,(b.ReturnNums-IFNULL(c.SortingSum,0))/b.ReturnNums) AS DestoriedRate
            //FROM 
            //(SELECT dn.ProjectId,pj.`Name`,td.MaterialId,IFNULL(SUM(td.PackNums),0) AS PackNums
            //FROM PB_TransportBillDetail td		
            //JOIN PB_TransportBill tb ON tb.Id=td.ParentId AND tb.BillType='Delivery' AND TO_DAYS(NOW())-TO_DAYS(tb.IntoTime)<=29		
            //JOIN PB_TransportBillGroup ng ON ng.ParentId=tb.Id
            //JOIN PB_DeliveryNotice dn ON dn.Id=ng.DeliveryNoticeId
            //JOIN PB_Project pj ON dn.ProjectId=pj.Id
            //GROUP BY dn.ProjectId,td.MaterialId
            //ORDER BY dn.ProjectId) a
            //LEFT JOIN 
            //(SELECT ProjectId,MaterialId,IFNULL(SUM(ReturnNums),0) AS ReturnNums
            //FROM PB_ReturnDetailLog
            //WHERE TO_DAYS(NOW())-TO_DAYS(CreateTime)<=29	
            //GROUP BY ProjectId,MaterialId) b
            //ON a.ProjectId=b.ProjectId AND a.MaterialId=b.MaterialId
            //LEFT JOIN
            //(SELECT rs.ProjectId,sd.MaterialId,IFNULL(SUM(sd.SortingSum),0) AS SortingSum
            //FROM PB_ReturnSortDetail sd 
            //JOIN PB_ReturnSorting rs ON sd.ParentId=rs.Id AND rs.State=1 AND TO_DAYS(NOW())-TO_DAYS(sd.CreateTime)<=29		
            //GROUP BY rs.ProjectId,sd.MaterialId) c
            //ON b.ProjectId=c.ProjectId AND b.MaterialId=c.MaterialId
            //ORDER BY a.ProjectId,a.MaterialId) t
            //GROUP BY t.ProjectId 
            //ORDER BY ReturnRate";
            //按照项目分组的回货率、损坏率
            string strSQL = @"SELECT a.ProjectId,a.Name AS ProjectName,
IF(a.PackNums IS NULL,0,IF(b.ReturnNums>a.PackNums,1,IFNULL(b.ReturnNums,0)/a.PackNums))*100 AS ReturnRate,
IF(b.ReturnNums IS NULL OR c.SortingSum>b.ReturnNums,0,(b.ReturnNums-IFNULL(c.SortingSum,0))/b.ReturnNums)*100 AS DestoriedRate
FROM 
(SELECT dl.ProjectId,pj.`Name`,IFNULL(SUM(td.PackNums),0) AS PackNums
FROM PB_TransportBillDetail td		
JOIN PB_TransportBill tb ON tb.Id=td.ParentId AND tb.BillType='Delivery'  AND TO_DAYS(NOW())-TO_DAYS(tb.DepartureTime)<=29		
JOIN PB_DesignDetail dd ON td.DesignDetailId=dd.Id
JOIN PB_DesignList dl ON dd.ParentId=dl.Id
JOIN PB_Project pj ON dl.ProjectId=pj.Id
GROUP BY dl.ProjectId) a
LEFT JOIN 
(SELECT ProjectId,IFNULL(SUM(ReturnNums),0) AS ReturnNums
FROM PB_ReturnDetailLog
WHERE TO_DAYS(NOW())-TO_DAYS(CreateTime)<=29	
GROUP BY ProjectId) b
ON a.ProjectId=b.ProjectId
LEFT JOIN
(SELECT rs.ProjectId,IFNULL(SUM(sd.SortingSum),0) AS SortingSum
FROM PB_ReturnSortDetail sd 
JOIN PB_ReturnSorting rs ON sd.ParentId=rs.Id AND rs.State=1 AND TO_DAYS(NOW())-TO_DAYS(sd.CreateTime)<=29		
GROUP BY rs.ProjectId) c
ON b.ProjectId=c.ProjectId
ORDER BY DestoriedRate DESC";
            var res = await Db.GetListBySqlAsync<PB_ProjectReturnDestoriedRateDTO>(strSQL);
            if (res.Count > 0)
            {
                res.ForEach(aData => {
                    aData.ReturnRate = Convert.ToDouble(aData.ReturnRate.ToString("#0.##"));
                    aData.DestoriedRate = Convert.ToDouble(aData.DestoriedRate.ToString("#0.##"));
                });
            }
            return res;
        }
        /// <summary>
        /// 大屏显示30天项目回货率(先计算单品回货率，再求平均数)，损坏率列表。
        /// </summary>
        /// <returns>30天项目回货率，损坏率</returns>
        public async Task<List<PB_ProjectReturnDestoriedRateDTO>> Get30DayReturnDestoriedRate2()
        {
            //按照项目分组的回货率、损坏率
            //            string strSQL = @"SELECT a.ProjectId,a.Name AS ProjectName,
            //IF(a.PackNums IS NULL,0,IF(b.ReturnNums>a.PackNums,1,IFNULL(b.ReturnNums,0)/a.PackNums))*100 AS ReturnRate,
            //IF(b.ReturnNums IS NULL OR c.SortingSum>b.ReturnNums,0,(b.ReturnNums-IFNULL(c.SortingSum,0))/b.ReturnNums)*100 AS DestoriedRate
            //FROM 
            //(SELECT dl.ProjectId,pj.`Name`,IFNULL(SUM(td.PackNums),0) AS PackNums
            //FROM PB_TransportBillDetail td		
            //JOIN PB_TransportBill tb ON tb.Id=td.ParentId AND tb.BillType='Delivery'  AND TO_DAYS(NOW())-TO_DAYS(tb.DepartureTime)<=29		
            //JOIN PB_DesignDetail dd ON td.DesignDetailId=dd.Id
            //JOIN PB_DesignList dl ON dd.ParentId=dl.Id
            //JOIN PB_Project pj ON dl.ProjectId=pj.Id
            //GROUP BY dl.ProjectId) a
            //LEFT JOIN 
            //(SELECT ProjectId,IFNULL(SUM(ReturnNums),0) AS ReturnNums
            //FROM PB_ReturnDetailLog
            //WHERE TO_DAYS(NOW())-TO_DAYS(CreateTime)<=29	
            //GROUP BY ProjectId) b
            //ON a.ProjectId=b.ProjectId
            //LEFT JOIN
            //(SELECT rs.ProjectId,IFNULL(SUM(sd.SortingSum),0) AS SortingSum
            //FROM PB_ReturnSortDetail sd 
            //JOIN PB_ReturnSorting rs ON sd.ParentId=rs.Id AND rs.State=1 AND TO_DAYS(NOW())-TO_DAYS(sd.CreateTime)<=29		
            //GROUP BY rs.ProjectId) c
            //ON b.ProjectId=c.ProjectId
            //ORDER BY DestoriedRate DESC";
            string strSQL = @"SELECT dn.ProjectId,pj.`Name` AS ProjectName,td.MaterialId,IFNULL(SUM(td.PackNums),0) AS PackNums
            FROM PB_TransportBillDetail td		
            JOIN PB_TransportBill tb ON tb.Id=td.ParentId AND tb.BillType='Delivery' AND TO_DAYS(NOW())-TO_DAYS(tb.IntoTime)<=29		
            JOIN PB_TransportBillGroup ng ON ng.ParentId=tb.Id
            JOIN PB_DeliveryNotice dn ON dn.Id=ng.DeliveryNoticeId
            JOIN PB_Project pj ON dn.ProjectId=pj.Id
            GROUP BY dn.ProjectId,td.MaterialId";
            var res = await Db.GetListBySqlAsync<PB_ProjectReturnDestoriedRateDTO>(strSQL);
            strSQL = @"SELECT ProjectId,MaterialId,IFNULL(SUM(ReturnNums),0) AS ReturnNums
            FROM PB_ReturnDetailLog
            WHERE TO_DAYS(NOW())-TO_DAYS(CreateTime)<=29	
            GROUP BY ProjectId,MaterialId";
            var res1 = await Db.GetListBySqlAsync<PB_ProjectReturnDestoriedRateDTO>(strSQL);
            strSQL = @"SELECT rs.ProjectId,sd.MaterialId,IFNULL(SUM(sd.SortingSum),0) AS SortingSum
            FROM PB_ReturnSortDetail sd 
            JOIN PB_ReturnSorting rs ON sd.ParentId=rs.Id AND rs.State=1 AND TO_DAYS(NOW())-TO_DAYS(sd.CreateTime)<=29		
            GROUP BY rs.ProjectId,sd.MaterialId";
            var res2 = await Db.GetListBySqlAsync<PB_ProjectReturnDestoriedRateDTO>(strSQL);
            //计算损坏率（(回货数-分拣数)/回货数）
            foreach (var item1 in res1)
            {
                foreach (var item2 in res2)
                {
                    if (item1.ProjectId == item2.ProjectId && item1.MaterialId == item2.MaterialId)
                    {
                        if (item1.ReturnNums == 0 || item1.ReturnNums < item2.SortingSum)
                        {
                            item1.DestoriedRate = 0;
                        }
                        else
                        {
                            item1.DestoriedRate = (item1.ReturnNums - item2.SortingSum) / item1.ReturnNums;
                        }
                    }
                }
            }
            foreach (var item in res)
            {
                foreach (var item1 in res1)
                {
                    if (item.ProjectId == item1.ProjectId && item.MaterialId == item1.MaterialId)
                    {
                        item.DestoriedRate = item1.DestoriedRate;
                        if (item.PackNums == 0)
                        {
                            item.ReturnRate = 0;
                        }
                        else if (item.PackNums < item1.ReturnNums)
                        {
                            item.ReturnRate = 1;
                        }
                        else
                        {
                            item.ReturnRate = item1.ReturnNums / item.PackNums;
                        }
                    }
                }
            }
            res = res.GroupBy(m => new { m.ProjectId }).Select(g => new PB_ProjectReturnDestoriedRateDTO 
            { ProjectId=g.Key.ProjectId,
              ProjectName=g.FirstOrDefault().ProjectName,
              ReturnRate=g.Sum(r=>r.ReturnRate)/g.Count(),
              DestoriedRate = g.Sum(r=>r.DestoriedRate)/g.Count()
             }).ToList();

            if (res.Count > 0)
            {
                res.ForEach(aData => {
                    aData.ReturnRate = Convert.ToDouble(aData.ReturnRate.ToString("#0.##"));
                    aData.DestoriedRate = Convert.ToDouble(aData.DestoriedRate.ToString("#0.##"));
                });
            }
            return res;
        }
        /// <summary>
        /// 钉钉项目列表展示项目进度数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<PB_ProjectDTO>> GetProjectItemAsync(PageInput<ConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_Project, Base_Department, Base_User, Base_Region, Base_Region, Base_Region, PB_ProjectDTO>> select = (a, b, c, d, e, f) => new PB_ProjectDTO
            {
                DepartmentName = b.Name,
                ContactsName = c.UserName,
                ProvinceName = d.Name,
                CityName = e.Name,
                AreaName = f.Name,
                PatrolInspection = a.PatrolInspection,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_User>() on a.ContactsId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_Region>() on a.ProvinceId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<Base_Region>() on a.CityId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    join f in Db.GetIQueryable<Base_Region>() on a.AreaId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f);
            if (!search.Keyword.IsNullOrEmpty())
            {
                var keyword = $"%{search.Keyword}%";
                q = q.Where(x =>
                      EF.Functions.Like(x.Code, keyword)
                      || EF.Functions.Like(x.Name, keyword));
            }

            //项目档案权限
            q = q.OrderByDescending(p => p.CreateTime);
            q = q.Where(x => x.State == 0);
            return await q.GetPageResultAsync(input);
        }


        /// <summary>
        /// 大屏获取回货项目数据
        /// </summary>
        /// <returns></returns>
        public async Task<List<ProjectReturnScreen>> ProjectReturnScreensList()
        {

            string sql = "SELECT b.Name ProjectName, d.Name MateriaName, IFNULL(sum(a.ReturnNums), 0) AS ReturnNums FROM PB_ReturnDetailLog AS a LEFT JOIN PB_Project AS b ON a.ProjectId = b.Id LEFT JOIN PB_Material AS c ON a.MaterialId = c.Id  LEFT JOIN PB_MaterialType AS d ON c.MaterialTypeId = d.Id  GROUP BY a.ProjectId,d.Id  ORDER BY a.ProjectId,	d.Id";
            var ProjectReturnList = await Db.GetListBySqlAsync<ProjectReturnScreen>(sql);
            if (ProjectReturnList.Count > 0)
            {
                return ProjectReturnList;
            }
            else
            {
                throw new BusException("暂无数据");
            }
        }

        public async Task<PageResult<StatisticalData>> GetStatisticalDataAsync(PageInput<ProjectReturnConditionDTO> input)
        {
            StringBuilder StatisticsDatas = new StringBuilder();
            StatisticsDatas.AppendLine(" -- 项目看板 sql 调整");
            StatisticsDatas.AppendLine("-- 项目看板 表格调");
            StatisticsDatas.AppendLine("SELECT -- 设计清单信息");
            StatisticsDatas.AppendLine("OK.BuildingNumber,");
            StatisticsDatas.AppendLine("OK.RealNums 'DesignQuantity',");
            StatisticsDatas.AppendLine("OK.`MaterialCode` 'number',");
            StatisticsDatas.AppendLine("OK.MaterialName 'ProductName',");
            StatisticsDatas.AppendLine("OK.ParentId,");
            StatisticsDatas.AppendLine("OK.MaterialId,");
            StatisticsDatas.AppendLine("OK.buildingID, -- 备货单数据");
            StatisticsDatas.AppendLine("IFNULL(PB_PickUpList.PreparedQuantity, 0) PreparedQuantity, -- 发货数据");
            StatisticsDatas.AppendLine("IFNULL(PB_PickUpList.IssuedQuantity, 0) IssuedQuantity, -- 回货数据");
            StatisticsDatas.AppendLine("IFNULL(ReturnData.ReturnNums, 0) ReturnNums,-- 获取入库数量");
            StatisticsDatas.AppendLine("IFNULL(ReceiptWarehousing.ReceiptQuantity, 0) ReceiptQuantity");
            StatisticsDatas.AppendLine("FROM");
            StatisticsDatas.AppendLine(" (");
            StatisticsDatas.AppendLine("SELECT");
            StatisticsDatas.AppendLine(" SUM(Nums) AS 'Nums',");
            StatisticsDatas.AppendLine("  SUM(RealNums) AS 'RealNums',");
            StatisticsDatas.AppendLine(" ParentId AS 'ParentId',");
            StatisticsDatas.AppendLine("   MaterialId AS 'MaterialId',");
            StatisticsDatas.AppendLine("  MaterialName AS 'MaterialName',");
            StatisticsDatas.AppendLine("MaterialCode AS 'MaterialCode',");
            StatisticsDatas.AppendLine(" BuildingNumber AS 'BuildingNumber',");
            StatisticsDatas.AppendLine(" buildingID AS 'buildingID'");
            StatisticsDatas.AppendLine(" FROM");
            StatisticsDatas.AppendLine(" (");
            StatisticsDatas.AppendLine(" SELECT");
            StatisticsDatas.AppendLine("building.Buildingnumber BuildingNumber,");
            StatisticsDatas.AppendLine(" building.Id buildingID,");
            StatisticsDatas.AppendLine("M.`Name` MaterialName,");
            StatisticsDatas.AppendLine("M.`Code` MaterialCode, ");
            StatisticsDatas.AppendLine("a.ParentId,");
            StatisticsDatas.AppendLine("a.MaterialId AS MaterialId,");
            StatisticsDatas.AppendLine("a.Nums,");
            StatisticsDatas.AppendLine("a.Slot,");
            StatisticsDatas.AppendLine("a.RealNums,");
            StatisticsDatas.AppendLine("a.State,");
            StatisticsDatas.AppendLine("a.Id AS SubId");
            StatisticsDatas.AppendLine("FROM");
            StatisticsDatas.AppendLine("PB_DesignDetail AS a");
            StatisticsDatas.AppendLine("LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id");
            StatisticsDatas.AppendLine("LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id");
            StatisticsDatas.AppendLine("LEFT JOIN PB_Buildingnumber AS building ON List.BuildingNumberID = building.Id");
            StatisticsDatas.AppendLine("WHERE");
            StatisticsDatas.AppendLine("a.State = 0");
            StatisticsDatas.AppendLine("AND List.ProjectId = '" + input.Search.Keyword + "' UNION ALL");
            StatisticsDatas.AppendLine("SELECT");
            StatisticsDatas.AppendLine("building.Buildingnumber AS 'BuildingNumber',");
            StatisticsDatas.AppendLine("building.Id buildingID,");
            StatisticsDatas.AppendLine("M.`Name` MaterialName,");
            StatisticsDatas.AppendLine("M.`Code` MaterialCode,");
            StatisticsDatas.AppendLine("a.ParentId,");
            StatisticsDatas.AppendLine("a.MaterialId AS MaterialId,");
            StatisticsDatas.AppendLine("a.Nums,");
            StatisticsDatas.AppendLine("a.Slot,");
            StatisticsDatas.AppendLine("a.RealNums,");
            StatisticsDatas.AppendLine("a.State,");
            StatisticsDatas.AppendLine("a.Id AS SubId");
            StatisticsDatas.AppendLine("FROM");
            StatisticsDatas.AppendLine("PB_DesignDetail AS a");
            StatisticsDatas.AppendLine("LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id");
            StatisticsDatas.AppendLine("LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id");
            StatisticsDatas.AppendLine("LEFT JOIN PB_Buildingnumber AS building ON List.BuildingNumberID = building.Id");
            StatisticsDatas.AppendLine("WHERE");
            StatisticsDatas.AppendLine("State = 1");
            StatisticsDatas.AppendLine("AND List.ProjectId = '" + input.Search.Keyword + "' UNION ALL");
            StatisticsDatas.AppendLine("SELECT");
            StatisticsDatas.AppendLine("building.Buildingnumber AS 'BuildingNumber',");
            StatisticsDatas.AppendLine("building.Id buildingID,");
            StatisticsDatas.AppendLine("M.`Name` MaterialName,");
            StatisticsDatas.AppendLine("M.`Code` MaterialCode,");
            StatisticsDatas.AppendLine("a.ParentId,");
            StatisticsDatas.AppendLine(" a.MaterialId AS MaterialId,");
            StatisticsDatas.AppendLine("(-a.Nums) AS Nums,");
            StatisticsDatas.AppendLine(" a.Slot,");
            StatisticsDatas.AppendLine("(-a.RealNums) AS RealNums,");
            StatisticsDatas.AppendLine("a.State,");
            StatisticsDatas.AppendLine("a.Id AS SubId");
            StatisticsDatas.AppendLine("FROM");
            StatisticsDatas.AppendLine("PB_DesignDetail AS a");
            StatisticsDatas.AppendLine("LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id");
            StatisticsDatas.AppendLine("LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id");
            StatisticsDatas.AppendLine("LEFT JOIN PB_Buildingnumber AS building ON List.BuildingNumberID = building.Id");
            StatisticsDatas.AppendLine("WHERE");
            StatisticsDatas.AppendLine("a.State = 2");
            StatisticsDatas.AppendLine("AND List.ProjectId = '" + input.Search.Keyword + "'");
            StatisticsDatas.AppendLine(") AS k");
            StatisticsDatas.AppendLine("GROUP BY");
            StatisticsDatas.AppendLine("MaterialId,");
            StatisticsDatas.AppendLine("ParentId,");
            StatisticsDatas.AppendLine("MaterialName,");
            StatisticsDatas.AppendLine("BuildingNumber,");
            StatisticsDatas.AppendLine("MaterialCode,");
            StatisticsDatas.AppendLine("buildingID");
            StatisticsDatas.AppendLine("ORDER BY");
            StatisticsDatas.AppendLine("BuildingNumber,");
            StatisticsDatas.AppendLine("MaterialCode");
            StatisticsDatas.AppendLine(" ) AS OK  -- 获取备货单数量");
            StatisticsDatas.AppendLine("LEFT JOIN(");
            StatisticsDatas.AppendLine("SELECT");
            StatisticsDatas.AppendLine("a.MaterialId AS MaterialId,");
            StatisticsDatas.AppendLine("IFNULL(sum(a.ReadyNums ), 0 ) PreparedQuantity,");
            StatisticsDatas.AppendLine("IFNULL(sum(C.OutNum), 0) IssuedQuantity,");
            StatisticsDatas.AppendLine("List.ParentId");
            StatisticsDatas.AppendLine("FROM");
            StatisticsDatas.AppendLine("PB_PickUpList AS a");
            StatisticsDatas.AppendLine("LEFT JOIN PB_DesignDetail AS List ON a.SubId = List.Id");
            StatisticsDatas.AppendLine("LEFT JOIN TD_OutStorage AS TD_OutStorage ON a.ParentId = TD_OutStorage.RefCode");
            StatisticsDatas.AppendLine("LEFT JOIN TD_OutStorRecord AS C ON TD_OutStorage.Id = C.OutStorId");
            StatisticsDatas.AppendLine("AND C.MaterialId = List.MaterialId");
            StatisticsDatas.AppendLine("GROUP BY");
            StatisticsDatas.AppendLine("a.MaterialId,");
            StatisticsDatas.AppendLine("ParentId");
            StatisticsDatas.AppendLine(") AS PB_PickUpList ON PB_PickUpList.ParentId = OK.ParentId");
            StatisticsDatas.AppendLine("AND PB_PickUpList.MaterialId = OK.MaterialId");
            StatisticsDatas.AppendLine("LEFT JOIN(");
            StatisticsDatas.AppendLine("SELECT");
            StatisticsDatas.AppendLine("IFNULL(Sum(returnss.ThisReturnNums ), 0 ) AS ReturnNums,");
            StatisticsDatas.AppendLine("returnss.BuildingId,");
            StatisticsDatas.AppendLine("returnss.MaterialId");
            StatisticsDatas.AppendLine("FROM");
            StatisticsDatas.AppendLine(" PB_Return AS log");
            StatisticsDatas.AppendLine(" LEFT JOIN PB_ReturnDetailLog AS returnss ON returnss.ParentId = log.Id");
            StatisticsDatas.AppendLine("GROUP BY");
            StatisticsDatas.AppendLine("returnss.BuildingId,");
            StatisticsDatas.AppendLine("returnss.MaterialId");
            StatisticsDatas.AppendLine(") AS ReturnData ON ReturnData.BuildingId = OK.buildingID");
            StatisticsDatas.AppendLine("AND ReturnData.MaterialId = OK.MaterialId-- 获取入库数量");
            StatisticsDatas.AppendLine("LEFT JOIN(");
            StatisticsDatas.AppendLine("SELECT");
            StatisticsDatas.AppendLine("IFNULL(SUM(Detailtable.Num), 0) ReceiptQuantity,");
            StatisticsDatas.AppendLine("Returntable.BuildingId,");
            StatisticsDatas.AppendLine("Detailtable.MaterialId");
            StatisticsDatas.AppendLine("FROM");
            StatisticsDatas.AppendLine("TD_InStorDetail AS Detailtable");
            StatisticsDatas.AppendLine("LEFT JOIN TD_InStorage AS Maintable ON Detailtable.InStorId = Maintable.Id");
            StatisticsDatas.AppendLine("LEFT JOIN TD_Receiving AS Receivingtable ON Maintable.RecId = Receivingtable.Id");
            StatisticsDatas.AppendLine("LEFT JOIN PB_Return AS Returntable ON Receivingtable.OrderId = Returntable.Id");
            StatisticsDatas.AppendLine("GROUP BY");
            StatisticsDatas.AppendLine("Returntable.BuildingId,");
            StatisticsDatas.AppendLine("Detailtable.MaterialId");
            StatisticsDatas.AppendLine(") AS ReceiptWarehousing ON ReceiptWarehousing.BuildingId = OK.buildingID");
            StatisticsDatas.AppendLine("AND ReceiptWarehousing.MaterialId = OK.MaterialId");
          var res = await Db.GetListBySqlAsync<StatisticalData>(StatisticsDatas.ToString());
          
            int pagecount;
            List<StatisticalData> listpro_down = res.ToList();
            List<StatisticalData> productes = new List<StatisticalData>();
            int pagees = listpro_down.Count;
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            #region


            #endregion
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();
            return new PageResult<StatisticalData> { Data = productes, Total = pagees, };
        }

        //Projectsunder IPB_ProjectBusiness.GetProjectsunder()
        //{
        //    throw new NotImplementedException();
        //}

        //TotalNumber IPB_ProjectBusiness.GetTotalNumber()
        //{
        //    throw new NotImplementedException();
        //}
        #endregion

        #region 私有成员

        #endregion
    }
}